cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Twelve
Frequent Visitor

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

Hi @Twelve 

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

@Twelve 

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

@Twelve 

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

Twelve
Frequent Visitor

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

@Twelve , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

AlB
Super User III
Super User III

Hi @Twelve 

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

Twelve
Frequent Visitor

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.