Forum Discussion

radissoufan's avatar
radissoufan
New Member
1 year ago
Solved

power bi function ALL strange behavior

Hello, I have single simple DAX Measure 2 = CALCULATE(     COUNTA(Query1[target_lfs_agg.f_m_id]),             ALL(Query1[target_lfs_agg.age_group])                 ) the result is s...
Need Help
  • radissoufan's avatar
    radissoufan
    1 year ago

    first one is

    Measure 2 = CALCULATE(
        COUNTA(Query1[target_lfs_agg.f_m_id]),
                ALL(Query1[target_lfs_agg.age_group])
                    )
    second one is

    Measure 2 = CALCULATE(
        COUNTA(Query1[target_lfs_agg.f_m_id]),
                ALL(Query1[target_lfs_agg.age_group_id])
                    )

    the strange thing is that the result of the first image finally gave me a correct result when i included the (id) column which i used in sorting the description column with the all() function 

    Measure 2 = CALCULATE(
        COUNTA(Query1[target_lfs_agg.f_m_id]),
                ALL(Query1[target_lfs_agg.age_group]),
                ALL(Query1[target_lfs_agg.age_group_id])
                    )
radissoufan's avatar
radissoufan
New Member
1 year ago

first one is

Measure 2 = CALCULATE(
    COUNTA(Query1[target_lfs_agg.f_m_id]),
            ALL(Query1[target_lfs_agg.age_group])
                )
second one is

Measure 2 = CALCULATE(
    COUNTA(Query1[target_lfs_agg.f_m_id]),
            ALL(Query1[target_lfs_agg.age_group_id])
                )

the strange thing is that the result of the first image finally gave me a correct result when i included the (id) column which i used in sorting the description column with the all() function 

Measure 2 = CALCULATE(
    COUNTA(Query1[target_lfs_agg.f_m_id]),
            ALL(Query1[target_lfs_agg.age_group]),
            ALL(Query1[target_lfs_agg.age_group_id])
                )
  • FBergamaschi's avatar
    FBergamaschi
    Super User
    1 year ago

    OK now I got it

     

    Whenever you sort a column by abohter one, you inject an additional filter that you were not removing

     

    So, to get the behaviour you want

     

    CALCULATE(
        COUNTA(Query1[target_lfs_agg.f_m_id]),
                ALL(Query1[target_lfs_agg.age_group]),
                ALL(Query1[target_lfs_agg.age_group_id])
    )
     
    This is true only beacuse you sorted the age_group by age_group_id and it is true only in Power BI and not in Power Pivot where your original code would have worked correctly
     

    If this helped, please consider giving kudos and mark as a solution

    me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

    • FBergamaschi's avatar
      FBergamaschi
      Super User
      1 year ago

      If my post solved and clarified, pls mark it as a solution so others can benefit from it (or mark somebody else's reply as such)

       

      Thanks