Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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) )
Solved! Go to 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
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
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
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
@nadavbi - You could use an AND slicer - https://community.powerbi.com/t5/Quick-Measures-Gallery/Patient-Cohort-AND-Slicer/m-p/391883#M130
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |