Limit measure to a dynamic list of IDs

[nadavbi]

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

[v-eqin-msft]

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.

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 · Accepted solution]

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:

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]

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]

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

[nadavbi]

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.