Forum Discussion

filip1150's avatar
filip1150
Frequent Visitor
7 years ago
Solved

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 ...
Need Help
  • d_gosbell's avatar
    d_gosbell
    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's avatar
filip1150
Frequent Visitor
7 years ago

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" 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

filip1150's avatar
filip1150
Frequent Visitor
7 years ago

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.

 

YearGenderMaritalStatusm
2017MaleSingle2017/Male/Single
2017MaleMarried2017/Male/Married
2017MaleDivorced2017/Male/Divorced
2017MaleAll Marital Statuses2017/Male
2017FemaleSingle2017/Female/Single
2017FemaleMarried2017/Female/Married
2017FemaleDivorced2017/Female/Divorced
2017FemaleAll Marital Statuses2017/Female
2017All gendersSingle2017/Single
2017All gendersMarried2017/Married
2017All gendersDivorced2017/Divorced
2017All gendersAll Marital Statuses2017
2018MaleSingle2018/Male/Single
2018MaleMarried2018/Male/Married
2018MaleDivorced2018/Male/Divorced
2018MaleAll Marital Statuses2018/Male
2018FemaleSingle2018/Female/Single
2018FemaleMarried2018/Female/Married
2018FemaleDivorced2018/Female/Divorced
2018FemaleAll Marital Statuses2018/Female
2018All gendersSingle2018/Single
2018All gendersMarried2018/Married
2018All gendersDivorced2018/Divorced
2018All gendersAll Marital Statuses2018
2019MaleSingle2019/Male/Single
2019MaleMarried2019/Male/Married
2019MaleDivorced2019/Male/Divorced
2019MaleAll Marital Statuses2019/Male
2019FemaleSingle2019/Female/Single
2019FemaleMarried2019/Female/Married
2019FemaleDivorced2019/Female/Divorced
2019FemaleAll Marital Statuses2019/Female
2019All gendersSingle2019/Single
2019All gendersMarried2019/Married
2019All gendersDivorced2019/Divorced
2019All gendersAll Marital Statuses2019
  • d_gosbell's avatar
    d_gosbell
    Super User
    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
    • d_gosbell's avatar
      d_gosbell
      Super User
      7 years ago

      filip1150 How did you get on with this? Did it work for you? Was the performance acceptible over your full data set?

      • filip1150's avatar
        filip1150
        Frequent Visitor
        7 years ago

        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