Distinct Count Based on Multiple Selections of a Slicer Created from Multiple Columns

[msraymch]

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

[v-frfei-msft]

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

[msraymch]

@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!

[amitchandak]

@msraymch ,

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

@msraymch  

[Greg_Deckler]

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