DAX - Creating a measure (based on a filtered table) that is not affected by the slicers

[Anonymous]

Hi everyone,

I am trying to create a measure that will count the number of unique values in a specific column in a table after it has been filtered based on values in another table. Both tables are linked. The difficulty is that I want that measure not to be affected by any slicers that I may have.

I tried to attach a .pbix that contains a data model that looks like the one I have but it doesn't seem to work.

Basically, I have my table F Group that has the colum Variable1 (which takes the values "Yes" or "No"). This table is linked to the table Ing Group (through several other tables) that has the colum Ing-ID. I want to create a measure that counts the number of unique values in the column Ing-ID when Variable1 from the F Group table is "Yes", and I want that measure not to be affected by the slicers.

So far, I came up with 

Nb_IngID = COUNTX(SUMMARIZE(ALL('Ing Group'), 'Ing Group'[Ing-ID]), 'Ing Group'[Ing-ID])

which is not affected by the slicers but it counts all the values in Ing-ID, even those for which Variable1 is "No". I can't find a way to introduce a FILTER function inside that measure.

Also, I know it would probably be easier to create another table, but that is unfortunately not an option as I can't create/change the model.

Thank you if you can help me!

[AlB · Accepted solution]

Hi @Anonymous 

You cannot attach the file, you have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

 Assuming there are relationships to propagate the filters in F down to ING:

Nb_IngID =
CALCULATE ( DISTINCTCOUNT ( 'Ing Group'[Ing-ID] ), 'F Group'[Variable] = "No" , ALL( ))

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

The ALL(  ) is because you stated you wanted no interference from any slicer, which is a bit of an odd/unusual  requirement.  If it demands an argument in DQ, you can just use ALL( ) with the tables/columns you expect to have slicers on. Or perhaps:

Nb_IngID =
CALCULATE (
    DISTINCTCOUNT ( 'Ing Group'[Ing-ID] ),
    'F Group'[Variable] = "No",
    ALL ( 'Ing Group' ),
    ALL ( 'F Group' )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB,

I did exactly what you suggested: I used ALL() with the tables that had slicers and it worked exactly as I needed!

I'm accepting your answer as Solution as it gives me exactly what I wanted and it is clear and elegant. Thank you very much for your help, I really appreciate it.

Have a good day!

[amitchandak]

@Anonymous , You can add filter like given example

Nb_IngID = COUNTX(SUMMARIZE(Filter(ALL('Ing Group'), 'Ing Group'[Ing-ID] = "X" ), 'Ing Group'[Ing-ID]), 'Ing Group'[Ing-ID])

You can use treatas to filter values from another table

https://docs.microsoft.com/en-us/dax/treatas-function

[AlB · Accepted solution]

Hi @Anonymous 

You cannot attach the file, you have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

 Assuming there are relationships to propagate the filters in F down to ING:

Nb_IngID =
CALCULATE ( DISTINCTCOUNT ( 'Ing Group'[Ing-ID] ), 'F Group'[Variable] = "No" , ALL( ))

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thank you for taking the time to help!

When I tried your formula, it worked as intended on the fake data model that I created but not on the real one. For the real one, it said that the function ALL() requires at least one parameter. I'm not sure why it worked on one but not on the other. The only difference is that on the real one I use a DirectQuery connexion.

Is there a way to tweak your formula so that it works with a DirectQuery connexion?