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