Forum Discussion
Dax Measure question
- 7 years ago
Just to set expectations, I want to start out by saying that this concept raises a number of red flags for me. I think it might be possible, but I don't know if the performance will scale as you add more columns and more combinations.
You are basically taking one of the worlds fastest aggregation engines, then loading in pre-aggregated data and only using it for filtering. And a good deal of the speed of the tabular engine behind Power BI comes from the fact that it uses a column store, so if you have a visual that only references 4 columns, Power BI only has to scan those 4 columns. But with your non-aggregatable data it will have to scan every "row" of all columns for every visual.
Based on your requirements and the sample data you provided I think the following measure will work
Measure = // Marital Status Filters VAR _maritalStatusFilter = FILTER(VALUES(Table1[MaritalStatus]), IF(ISINSCOPE(Table1[MaritalStatus]) , Table1[MaritalStatus] <> "All Marital Statuses" // if Marital Status is one of the output columns , Table1[MaritalStatus] = SELECTEDVALUE(Table1[MaritalStatus]) // if Marital Status has a single filter ) ) VAR _maritalStatusAll = FILTER(ALL(Table1[MaritalStatus]) , IF(NOT(HASONEVALUE(Table1[MaritalStatus])) // if Marital Status has multiple fiters , Table1[MaritalStatus] = "All Marital Statuses" ,FALSE() ) ) // Gender Filters VAR _genderFilter = FILTER(VALUES(Table1[Gender]), IF(ISINSCOPE(Table1[Gender]) , Table1[Gender] <> "All genders" , Table1[Gender] = SELECTEDVALUE(Table1[Gender]) ) ) VAR _genderAll = FILTER(ALL(Table1[Gender]) , IF(NOT(HASONEVALUE(Table1[Gender])) , Table1[Gender] = "All genders" ,FALSE() ) ) // Year Filter (does not need an "All" filter) VAR _yearFilter = FILTER(VALUES(Table1[Year]) , Table1[Year] = SELECTEDVALUE(Table1[Year]) ) Var result = CALCULATE(MAX(Table1[m]) , UNION(_genderFilter,_genderAll) , UNION( _maritalStatusFilter, _maritalStatusAll) , _yearFilter ) return result
filip1150 How did you get on with this? Did it work for you? Was the performance acceptible over your full data set?
Hi Darren
First of all, many thanks for your help.
I had to wait to get approval to install a newer version of Power BI (the one I had did not have the required DAX functions available).
I just got that this morning, so I will have to try and see what kind of performance I get. I will report back on that as soon as I have something, probably sometime next week