February 12, 2018 at 5:20 pm #9167
I’ve been trying to create a measure that returns a value for all possible combinations of attributes across multiple columns. In the attached file, the data model does not have any data for:
- Gender = “M”
- Language = “Spanish”
- Married = “Y”
That particular combination doesn’t even exist in the data model, and wont even return a “True” if Blank ().
I’ve tried multiple measures that would detect a blank values, but none seem to be able to handle non-existent combinations of lookups (Measures are included in the attached). I haven’t found any argument that will return a “TRUE/FALSE” value (or any other value for that matter)
PivotTable Options > Display > Show items with no data on rows / columns has no effect.
Ultimately, my goal is to build a measure that will remove filter criteria column-by-column that will apply a more aggregated value than the filter context can apply on it’s own (any suggestions on another way to attain what I’m trying to accomplish below would also be appreciated). e.g:
AVG Minutes (Adj):=IF ( [AVG Minutes] = Blank () || [AVG Minutes] = 0 ,
CALCULATE( [AVG Minutes] ,
ALL( Table1[Married] )
From what I’ve read, this seems to be an related to Autoexist within data models, but I’m not sure how to manipulate that. I’ve tinkered with ADDMISSINGITEMS() as well, but have had no luck understanding the syntax, and there isn’t much documentation other than Microsoft’s out there at the moment.
Is there anything that will detect a nonexistent combination and allow me to return a different value in IF () ?
Any help in would be appreciated, Thanks!
Attachments:You must be logged in to view attached files.February 12, 2018 at 8:17 pm #9178
There is a way to get the answer you are seeking, but you should know that the issue is not having just having the right measure: there is no way that a measure can create an entry in the Rows drop zone (or in a Columns drop zone) of a pivot.
Two questions for you: are you using Excel 2016? and What is your comfort level with Power Query?February 12, 2018 at 11:53 pm #9183
I am using Excel 2016 (32-bit, sadly), and I’ve spent a good portion of time in Power Query.February 13, 2018 at 2:11 am #9185
Basically, your formula thinking is correct.
However, when you use a single table data model, if you want all combinations represented on a pivot’s drop zone for Rows, you need to have at least one row for each combination of data.
So *one* way to fix that is to create a multi-dimensional model using Power Query, where each dimension represents all of the possibilities.
Please see attached workbook for such a solution. Although I used PQ to create lookup tables for all dimensions, probably in this case all that would be needed is a dimension for marital status.
Another way (not addressed in this workbook) is to start with an DAX query embedded in an Excel spreadsheet. If you would be interested in taking a look at such an idea, let me know and I will attach another workbook. The need to write DAX queries in Excel on fact tables has largely been replaced by Power Query since DAX queries in Excel can dramatically increase the workbook size.
Attachments:You must be logged in to view attached files.February 13, 2018 at 4:09 pm #9193
Well of course. Why are the simplest solutions always overlooked?
Guess I’m a little disappointed this didn’t occur to me. Apparently my ability to bend lookup tables and relationships to my will still hasn’t reached “Jedi Master” status. My coworker called this solution “elegant.”
I’ll try it on our real data and let you know if I have any issues.
SteveFebruary 13, 2018 at 4:46 pm #9194
Am glad to hear though that you asked your question in the PowerPivotPro forums!
Looking forward working with you again,
You must be logged in to reply to this topic.