Forum Discussion
Dax Measure question
Hi
I first posted this in the "DAX Commands and tips" but decided to copy it here as it seems mora appropriate,
I have a fact table based on data is already pre-aggregated at all levels. For privacy reasons I do not have access to the raw data.
Measures are things like count of people and median salary. Median salary is not really an additive measure and counts are neither (long explanation, is about some detailed data being hidden at very lower levels of granularity for privacy reasons).
Therefore I can't use any aggregate function to compute measures like averages, weighted averages, sums, they will have to be displayed the way they are stored.
My data will be a cross product of all dimensions members plus one "All" row for each dimension combination. Every dimension added to my data multiplies the number of rows by a factor of 1 (for all) plus the number of members in the dimension.
For instance, I will have dimensions like gender (Male/Female) and marital status (Married/Single), i will have the following rows
Male Single median_salary
Female Single median_salary
All genders Single median_salary
Male Married median_salary
Female Married median_salary
All genders Married median_salary
Male All Marital median_salary
Female All Marital median_salary
All genders All Marital median_salary
Data contains other dimensions with higher cardinality but I only have numbers available at each individual level, not for combinations. For instance if Marital status would also include "Divorced", I will get extra rows for "Divorced" but not metrics for combinations like "Divorced or Single", "Divorced or married" etc.
My challenge is to medel the data so that people can report on it. For instance if a report does not contain a "Gender" column I want to see the numbers (salary) corresponding to "All genders", but if data contains a Gender column or filter I want to show the numbers corresponding to the appropriate gender.
Also, if the data is filtered such as more members but not all are take, my dimension should be empty. (I.e. if you filter by multiple values and choose single and divorced then I want to see the measure empty as I don;t know the correct result)
I was thinking maybe the isFiltered function might help somehow, but I'm not quite sure, and my power BI / dax experience is limited.
Thanks!
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
8 Replies
Hi filip1150 ,
Could you please post some simple sample data and your desired result to have a test if possible? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Regards,
Daniel He
Good point, thank you.
Below is my data and here are the requirements:
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" rowsThe "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 2019Sorry,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" rowsThe "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
