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

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
Eyelyn9
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
Eyelyn9
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

Eyelyn9
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

 

 

nadavbi
Frequent Visitor

Hi @Eyelyn9 ,

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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!