Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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!

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

 

View solution in original post

AlB
Super User
Super User

@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 

 

SU18_powerbi_badge

 

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

@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 

 

SU18_powerbi_badge

 

Anonymous
Not applicable

@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
Super User
Super User

@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
Super User
Super User

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 

 

SU18_powerbi_badge

 

Anonymous
Not applicable

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.