power bi function ALL strange behavior

[radissoufan]

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 still filterd by the field,, why?.. isn't suppose to be all the same value? the strange is working correctly on naumbers types

[v-pnaroju-msft]

Thank you @FBergamaschi , @mdaatifraza5556 , and @AmiraBedh  for your responses.

Hi radissoufan,

Thank you for your update and for sharing your insights and approach to resolving the issue. Kindly continue to utilise the Fabric Community for any further assistance with your queries.

Thank you.

[AmiraBedh]

Hello !

Thank you for posting on Fabric community.

I think that the issue is about how Power BI is treating target_lfs_agg.age_group as a text column that is not truly disconnected from context.  When you use ALL(Query1[target_lfs_agg.age_group]) thisis removes the filter only on that column. Power BI re-applies the visual-level context to show a bar per category even though the measure ignores the filter from the column itself.

Your second visualuses target_lfs_agg.age_group_id which is numeric, (I assumed that you use it as a surrogate key) and because it's an integer and doesn’t carry any internal display logic or metadata (like formatted labels), Power BI doesn’t re-apply as much context automatically, especially if it's not used elsewhere in visuals or sorting.

So you should try :

Measure 2 = 
CALCULATE(
    COUNTA(Query1[target_lfs_agg.f_m_id]),
    REMOVEFILTERS(Query1[target_lfs_agg.age_group])
)

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

[radissoufan]

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]

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]

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

[mdaatifraza5556]

Hi @radissoufan 

I have tried with a sample dataset.

It's working fine.
Could you please share the PBIX file or the sample dataset?

Below is what I have tried.

Result = CALCULATE(

    COUNTA('Table'[f_m_id]),

    ALL('Table'[age_group])

)

I have also attached the pbix file you check it.

If this answers your questions, kindly accept it as a solution and give kudos.

[FBergamaschi]

You show two images

Can you please write here

The code executed in each and  what is the column grouped in the ROWS section in each?

ALL as a CALCULATE modifier removes filters so you should see the same value but that depends on which is the column on ehich you remove the filters vs which is the column you are grouping in ROWS

The behoaviour in the firts image seems incorrect at first glance, so please can you share the pbix via some cloud service

Thx

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

@mein 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