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
Sorry,posting again the data.
"m" is my measure. I chose it to be string for this example to make it more readable and to emphasize is cannot be aggregated.
If I create a table visual, I expect the following behavior:
1. If I do not filter/slice on year, the measure should be blank. I.e. creating a table without a "Year" column should result in all rows having an empty measure.
Also, if I filter /slice for multiple years the measures should be blank
2. If the table does not have a "Marital" column and there is no slicer/filter on Marital, I expect to show the values corresponding to the "All Marital Statuses rows. also, I cannot aggregate 2 differnt marital statuses on the same row - should be an empty measure.
Same goes for gender.
3. If my table contains a Year, Gender and Marital column, I do not want to see the rows that have Marital = "All Marital" or Gender"All Gender"
4. If the table contains only year and Measure and is not otherwise filtered by gender or Marital, I expect to see 3 rows (one for each year) corresponding to Gender= "All genders" AND Marital = "all marital" rows
The "All Gender" and "All Marital" rows act like subtotals and are included in the source data because their values cannot be inferred from lower level values.
The actual data contains about 50 columns and few hundred million rows.
If this is possible I might have a follow up question about hierarchies.
| Year | Gender | MaritalStatus | m |
| 2017 | Male | Single | 2017/Male/Single |
| 2017 | Male | Married | 2017/Male/Married |
| 2017 | Male | Divorced | 2017/Male/Divorced |
| 2017 | Male | All Marital Statuses | 2017/Male |
| 2017 | Female | Single | 2017/Female/Single |
| 2017 | Female | Married | 2017/Female/Married |
| 2017 | Female | Divorced | 2017/Female/Divorced |
| 2017 | Female | All Marital Statuses | 2017/Female |
| 2017 | All genders | Single | 2017/Single |
| 2017 | All genders | Married | 2017/Married |
| 2017 | All genders | Divorced | 2017/Divorced |
| 2017 | All genders | All Marital Statuses | 2017 |
| 2018 | Male | Single | 2018/Male/Single |
| 2018 | Male | Married | 2018/Male/Married |
| 2018 | Male | Divorced | 2018/Male/Divorced |
| 2018 | Male | All Marital Statuses | 2018/Male |
| 2018 | Female | Single | 2018/Female/Single |
| 2018 | Female | Married | 2018/Female/Married |
| 2018 | Female | Divorced | 2018/Female/Divorced |
| 2018 | Female | All Marital Statuses | 2018/Female |
| 2018 | All genders | Single | 2018/Single |
| 2018 | All genders | Married | 2018/Married |
| 2018 | All genders | Divorced | 2018/Divorced |
| 2018 | All genders | All Marital Statuses | 2018 |
| 2019 | Male | Single | 2019/Male/Single |
| 2019 | Male | Married | 2019/Male/Married |
| 2019 | Male | Divorced | 2019/Male/Divorced |
| 2019 | Male | All Marital Statuses | 2019/Male |
| 2019 | Female | Single | 2019/Female/Single |
| 2019 | Female | Married | 2019/Female/Married |
| 2019 | Female | Divorced | 2019/Female/Divorced |
| 2019 | Female | All Marital Statuses | 2019/Female |
| 2019 | All genders | Single | 2019/Single |
| 2019 | All genders | Married | 2019/Married |
| 2019 | All genders | Divorced | 2019/Divorced |
| 2019 | All genders | All Marital Statuses | 2019 |
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- d_gosbell7 years agoSuper User
filip1150 How did you get on with this? Did it work for you? Was the performance acceptible over your full data set?
- filip11507 years agoFrequent Visitor
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
- filip11507 years agoFrequent Visitor
d_gosbell
Navigating data in power BI is rather sluggish but not totally un-acceptable at this point.
Creating a table visual that includes the measure will take about 15-20 seconds for every click where you add or remove columns. The file size is about 0.6 GB. The base SQL table consists of about 120 million rows, including about 10 dimensions and 10 measures. This running an a pretty old computer, slow hdd, 16g GB ram, i5 4 core@ 3.3GHZ
My next question at this point what is the best way to hide the aggregate rows (i.e. if i select gender, I do not want to see the "All Genders". And how to deal with hierarchies - i.e. I have data at each level of aggregations and they will not roll up nicely)
- d_gosbell7 years agoSuper User
filip1150 wrote:My next question at this point what is the best way to hide the aggregate rows (i.e. if i select gender, I do not want to see the "All Genders". And how to deal with hierarchies - i.e. I have data at each level of aggregations and they will not roll up nicely)
Am I correct in assuming that you are talking about hiding these rows when the column is used in a slicer? If so there is no way at the moment that I know of to do this, but the Power BI team is working on a feature to allow for applying filters in slicers https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/12545673-visual-level-filtering-in-slicers which should address this (if my guess about your requirement here is correct)
filip1150 wrote:And how to deal with hierarchies - i.e. I have data at each level of aggregations and they will not roll up nicely)
Can you provide a table of data like you did with the gender/marital status example? I'm not entirely sure what the issue is here.