'IF and ONLY IF'/ Conditional Slicer

[sbonilla0809]

Hi I am trying to figure out how to do an 'if and only if' kind of slicer. I can't share the data that im working with but for purposes of my problem we will pretend a customer can  buy Apples, Oranges and Bananas. How can I create a column/measure to use as a slicer to show me customers that have [CASE1] ONLY bought Apples or [Case 2] ONLY Apples and Oranges? When I filter out lets say Bananas it will show me the customers that have bought apples and oranges [including those that bought bananas too] even though the quantity of bananas is not showing.

The Data structure has only one table with columns:  order_id ( pk) ,  customer_id,  fruit, purchase_date and other fields

The table that I have in my visualization looks like this:

QTY being a measure := count(orderid)

Customerid     Fruit             QTY

A                  Apple               1

                    Oranges           2

B                  Apple               3

                    Banana            2

C                 Banana             2

                    Oranges            1

                    Apple                1

D                 Apple                 4

E                  Banana             3

F                   Apple               1

                     Oranges           2

G                  Oranges          3

* Marked with colors the customers that should be kept when applying the filters in each 'Case'

In addition, how can I count those customers that are filtered (  1 in Case 1  , 2 in Case 2)

Thanks 🙂

[v-gizhi-msft]

Hi,

Please try to creaet a seperate slicer table first:

Then try this measure:

Measure = 
VAR CustomerIDCountRows =
    CALCULATE (
        COUNT ( 'Table'[CustomerID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[CustomerID] IN FILTERS ( 'Table'[CustomerID] )
        )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Slicer Table'[SlicerValue] ),
        "Case 1", IF ( MAX ( 'Table'[Fruit] ) = "Apple" && CustomerIDCountRows = 1, 1, 0 ),
        "Case 2", IF (
            (
                MAX ( 'Table'[Fruit] ) = "Apple"
                    || MAX ( 'Table'[Fruit] ) = "Oranges"
            )
                && CustomerIDCountRows = 2,
            1,
            0
        )
    )

When select one value in slicer, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[amitchandak]

Create measure like these

Customer wise ST =calculate(sum(Table[QTY]),allexcpet(Table[Customerid]))					
Only Apple = calculate(sum(Table[QTY]),allexcpet(Table[Customerid]),Table[Fruit]="Apple")
Only Orange = calculate(sum(Table[QTY]),allexcpet(Table[Customerid]),Table[Fruit]="Apple")
					
Apple CT =sumx(filter(summarize(Table,Table[Customerid],"_GT",[Customer wise ST],"_app",[Only Apple]),[_GT]=[_app]),[_app])

Orange CT =sumx(filter(summarize(Table,Table[Customerid],"_GT",[Customer wise ST],"_app",[Only Orange]),[_GT]=[_app]),[_app])

Use last 2 in a slicer like https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

[sbonilla0809]

hi @amitchandak ,

What if I want to use the "Fruit" as a multi-select slicer to see multiple combinations : ONLY apples or ONLY apples and bananas, ONLY apple and oranges and so on. The idea is to see which customers purchase only those kind of products.

Thank you so much for everything 🙂

[sbonilla0809]

I couldnt upload the pbix document but this is the data I have:

Orderid	Customerid	Fruit
1	A	Apple
2	A	Orange
3	A	Orange
4	B	Apple
5	B	Apple
6	B	Apple
7	B	Banana
8	B	Banana
9	C	Apple
10	C	Banana
11	C	Banana
12	C	Orange
13	D	Apple
14	D	Apple
15	D	Apple
16	D	Apple
17	E	Banana
18	E	Banana
19	E	Banana
20	F	Apple
21	F	Orange
22	F	Orange
23	G	Orange
24	G	Orange
25	G	Orange

Hope this is more helpful ! 

Thank you in advance 🙂

[v-gizhi-msft]

Hi,

If my answer has solved your issue, please mark it as a solution for others to see.
Thanks!

Best Regards,

Giotto

[v-gizhi-msft · Accepted solution]

Hi,

Please try to create a slicer table first:

SlicerTable = DISTINCT(SELECTCOLUMNS('Table',"Fruit",'Table'[Fruit]))

Then try this measure:

check = 
VAR t =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Fruit] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Customerid] IN DISTINCT ( 'Table'[Customerid] )
        )
    )
RETURN
    IF (
        CALCULATE (
            DISTINCTCOUNT ( 'SlicerTable'[Fruit] ),
            FILTER ( 'SlicerTable', 'SlicerTable'[Fruit] IN t )
        )
            = DISTINCTCOUNT ( 'SlicerTable'[Fruit] )
            && COUNTROWS ( t ) = DISTINCTCOUNT ( 'SlicerTable'[Fruit] ),
        1,
        0
    )

Apply this measure to the table visual by setting [check]=1, when select one or more than one value in fruit slicer, the result shows:

Here is my test pbix file:

pbix 

Best Regards,

Giotto