Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
I have a dataset that has roughly 15 columns of binary variables. I created 15 additional label columns where if they have a '1' they got a label and if they had a '0' they were blank. Using the label columns, I created the custom slicer from information previously posted on the forums:
SlicerTable = DISTINCT ( UNION ( VALUES ( Table1[Part One] ), VALUES ( Table1[Part Two] ), VALUES ( Table1[Part Three] ), VALUES ( Table1[Part Four] ), VALUES ( Table1[Part Five] ) ) )
I filtered out the blank category and it looks wonderful.
With this I'm trying to find the distinct count of people where if a user selects two or more categories it will count the distinct number of people that are in all the selected categories. I found this in the forums:
Measure = CALCULATE(DISTINCTCOUNT(
Table1[person_id],
FILTER(Table1[Category1] IN VALUES (SlicerTable[Category]) ||
FILTER(Table1[Category2] IN VALUES (SlicerTable[Category]) ||
.
.
.
FILTER(Table1[CategoryN) IN VALUES (SlicerTable(Category) ) )
If I use this measure with the slicer table that I created and select more than one category I get the count of people that are in either category. That makes sense with the logic that's there. However, I am trying to get the distinct count of people if they are in both categories. I tried something similar to the previous code with "&&" instead of "||" and get (Blank) which makes sense because nobody is in every category.
Any ideas on how to accomplish this / If it is possible?
Thanks!
Link to another forum that ends with another person with the same question:
https://community.powerbi.com/t5/Desktop/Once-Slicer-for-multiple-columns/td-p/407949
Hi @msraymch ,
We can only achieve that by hard code like that.
test1 =
VAR slicer =
VALUES ( SlicerTable[category1_label] )
VAR s1 =
CALCULATE (
COUNTROWS ( exampleCategory ),
FILTER ( exampleCategory, exampleCategory[category1_label] IN slicer )
)
VAR s2 =
CALCULATE (
COUNTROWS ( exampleCategory ),
FILTER ( exampleCategory, exampleCategory[category2_label] IN slicer )
)
VAR s3 =
CALCULATE (
COUNTROWS ( exampleCategory ),
FILTER ( exampleCategory, exampleCategory[category3_label] IN slicer )
)
VAR s4 =
CALCULATE (
COUNTROWS ( exampleCategory ),
FILTER ( exampleCategory, exampleCategory[category4_label] IN slicer )
)
VAR s5 =
CALCULATE (
COUNTROWS ( exampleCategory ),
FILTER ( exampleCategory, exampleCategory[category5_label] IN slicer )
)
RETURN
IF (
s1 <> BLANK ()
&& s2 <> BLANK ()
&& s3 = BLANK ()
&& s4 = BLANK ()
&& s5 = BLANK (),
CALCULATE (
DISTINCTCOUNT ( exampleCategory[id] ),
FILTER (
exampleCategory,
'exampleCategory'[category1_label] IN slicer
&& exampleCategory[category2_label] IN slicer
)
)
)
@Greg_Deckler Thank you for the reference. I'm still new to the PowerBI forums. @amitchandak Thank you for the response. However, I was unable to get that to work.
I've attached a link to an example categories dashboard below:
https://drive.google.com/drive/folders/1fvbeRO5TVIRgL5-Smv1ExXKsh5-w48QY
So I am creating a custom slicer based on multiple columns which are binary variable labels. They get the label if they have a 1 and are blank if they are 0. I removed the blanks from the slicer to look nicer. In the example dashboard, the measure test1 finds the union of the categories. So if I select two categories on the slicer it will give me category1 OR category2. I'm trying to get a measure that combines them and gives me category1 AND category2. test2 is (blank) most the time but will give me a count of 1 when all the categories are selected because there is one observation that does have a label for all of them.
Thank you for the responses. I've been working on this for awhile now and it's been driving me nuts!
Try a measure like
countx(summarize(
DISTINCT (
UNION (
VALUES ( Table1[Part One] ),
VALUES ( Table1[Part Two] ),
VALUES ( Table1[Part Three] ),
VALUES ( Table1[Part Four] ),
VALUES ( Table1[Part Five] )
)
),[[Part One]] )[[Part One]])
Sample data and expected results would be great. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |