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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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!:
The Definitive Guide to Power Query (M)

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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