ALL does not ignore filter, when specific value is selected

DAX DAX Calculations
1

Hello,
I have the following problem. I do not understand the behavior of ALL() in my specific scenario.
I prepared a sample PBIX that demonstrates it. I want the measure to ignore filters in Potential Rating (and Keep filters on Job Level)
ALL problem.pbix (27.0 KB)

It seems like ALL does not remove filter -

but only when a specific value in a column is selected.
Would you please be able to explain why this happens?
(I need this count without filter on Potential rating to calculate % of grand total)

Thank you

image
image873×736 60.4 KB

2

Hi,

If you manually count the rows of your table, it actually has 10 rows. so i believe this is the number that you would like to see

The error is that you are counting the rows of “Table”, but are removing filters from a specific column All( ‘Table’[Potential Rating]).

Changing your measure to the following, will give you the desired result :

M_ALL rating = CALCULATE(COUNTROWS('Table'), ALL('Table'))

Here is the working file for your reference:

ALL problem_EDNA_SOL_Japjeet.pbix (27.1 KB)

Hope this helps.

Regards,
Japjeet

3

Hello, thank you for your reply.
I actually want to remove filters only on Potential Rating. and keep filter on Job level = 17 or 18.
So the correct number is 9.
But when I filter on Potential Rating = Medium, it gives me 4.
Why ? ALL(Potential Rating) should remove the filter Potential Rating= Medium and give me 9

4

Hey @mara.treutel

Thanks for clarifying.

So, the bigger problem here is that you are working with a single flat table. What you need to do is to break the flat file into lookup and fact tables and build a model.

I have created 2 views for you to understand how the filters are eventually making it to your measure.

The top part is eth one that you created, from Flat table.

f1
f1957×595 22.5 KB

and the bottom part is the one that i have created by splitting out the Lookup tables.

f2
f2950×578 22.9 KB

You can clearly see the difference on the filters that are being applied to the measure, and

Do note that M_All rating2 is refers to Potential rating column of a Lookup table, and not the original flat table.

I have attached the working file for your reference.
ALL problem_EDNA_SOL_Japjeet.pbix (35.3 KB)

Hope this helps

Regards,
Japjeet

1 Like
5

Hello @jsodhi
yes thank you, making a star schema indeed solves the problem, so thank you very much for pointing me in the right direction.

I would like to ask more -
Is there some workaround/DAX syntax to make this work with only one flat table? often I create just a quick small reports, so I think for these scenarios it is an unnecessary struggle to create a dimension table for all the filters I might use. Is there some syntax that can make it work on the original data model?

6

Standard process in the forum is to only ask one question and open a new topic. This helps everyone in the forum to do proper searches if they are looking for something.

To answer your question, your dax formula will work better if your model is in star schema. Dimension table are issential if you plan on doing any kind of filters.