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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
nadavbi
Helper I
Helper I

Limit measure to a dynamic list of IDs

Say I have a fact table of IDs and product names. An ID can appear more than once if it has several products.

I would like to create a count distinct ID measure that takes into account other filter context (like dates), a selection from product slicer, and count all the IDs that also have somewhere a row with the chosen product slicer, regardless of other filters.

For example, if I choose the slicer to have product X, and the page is filtered on product Y, I would like to get the count of users that have X and Y.

What I'm imagining is a sort of a temporary list with all IDs that have X, and the measure checks if the counted IDs also exist in this list (in SQL it would be something like where id in (select id from temp_table) )

 

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @nadavbi ,

According to my understand , you want to count the ID based on the slicer value and page filter value ,right?

You could use the following formula:

productSlicer =
ALLSELECTED ( FactTable[Product Name] )
slicerID =
VAR _sele =
    SELECTEDVALUE ( productSlicer[Product Name] )
RETURN
CALCULATE ( SELECTEDVALUE ( FactTable[ID] ), FactTable[Product Name] = _sele )
pageFilterID =
VAR _filter =
    SELECTEDVALUE ( 'FactTable'[Product Name] )
RETURN
CALCULATE ( SELECTEDVALUE ( FactTable[ID] ), FactTable[Product Name] = _filter )

My visualizations look like this:

9.9.2.1.png

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

 

 

View solution in original post

5 REPLIES 5
v-eqin-msft
Community Support
Community Support

Hi @nadavbi ,

The two measures are used to specify what are selected  in Slicer and Page filter separately .

You could take a look at  my pbix file here.

slicer & page filter.gif

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

v-eqin-msft
Community Support
Community Support

Hi @nadavbi ,

According to my understand , you want to count the ID based on the slicer value and page filter value ,right?

You could use the following formula:

productSlicer =
ALLSELECTED ( FactTable[Product Name] )
slicerID =
VAR _sele =
    SELECTEDVALUE ( productSlicer[Product Name] )
RETURN
CALCULATE ( SELECTEDVALUE ( FactTable[ID] ), FactTable[Product Name] = _sele )
pageFilterID =
VAR _filter =
    SELECTEDVALUE ( 'FactTable'[Product Name] )
RETURN
CALCULATE ( SELECTEDVALUE ( FactTable[ID] ), FactTable[Product Name] = _filter )

My visualizations look like this:

9.9.2.1.png

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

 

 

Hi @v-eqin-msft ,

You understand correctly and it does look like the direction, but can you please elaborate on what's behind your totalCount visual? 

Also, what did you do to make the table present only the filter pane values (Paper), while the same column is also being filtered by Key.

Edit: I tried to reproduce your other steps, but I also don't understand how is this step possible:

slicerID =
VAR _sele =
    SELECTEDVALUE ( productSlicer[Product Name] )

You just defined productSlicer as a measure. How can you insert this into a SELECTEDVALUE function?

Thanks

Greg_Deckler
Super User
Super User

@nadavbi - You could use an AND slicer - https://community.powerbi.com/t5/Quick-Measures-Gallery/Patient-Cohort-AND-Slicer/m-p/391883#M130



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

I can't really use a slicer with multiple choices in this case. I get a filter over this column from 2 directions: one from drill through and one from a Slicer.

What I try to understand is how to create a temp list of IDs and check when I count IDs, that they all appear in this list. 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.