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