Need to create 2 filters which will show "Has" and "Does Not have" option

[manishgaikwad87]

I need help to create 2 slicers on one table. One with "Has" and Second with "Does Not Have".

Below is the table example which has "Customers", "Products" and "Products Price".

Customers	Product	Price
A	INT	1300
B	INT	1300
C	INT	1300
D	CHB	100
E	CHB	100
F	CHB	100
G	WND	2500
H	WND	2500
I	WND	2500
J	ECOM	1700
K	ECOM	1700
L	ECOM	1700
M	INT	1300

There should be 2 slicers, one with "Has Product" and second slicer should be "Does Not have products" which should filter out customers who have one product and does not have other products.

I have put the above table in below matrix format:-

Customers	CHB	ECOM	INT	WND	Grand Total
A			1300		1300
B			1300		1300
C			1300		1300
D	100				100
E	100				100
F	100				100
G				2500	2500
H				2500	2500
I				2500	2500
J		1700			1700
K		1700			1700
L		1700			1700
M			1300		1300
Grand Total	300	5100	5200	7500	18100

The slicers should perform in such a way that when i select "INT" in "Has" slicer and "CHB" in "Does not have" slicer it should show me only those customers who has product as "INT" and does not have product as "CHB".

[NamishB · Accepted solution]

Hi @manishgaikwad87 - there are many ways to do this. One of way as below-

Have 2 extra columns (HasINT) and (HasCHB)

formula for these would be something like- 

= Table.AddColumn(#"Changed Type", "HasInt", each if [INT] = null then "Does Not have" else "Has")

= Table.AddColumn(#"Added Custom", "HasCHB", each if [CHB] = null then "Does Not have" else "Has")

table in query editor looks like-

In Report panel-

Add 2 slicers one for each HasInt and HasCHB (Make sure in Format> edit Interactions> Stop filtering each slicers based on selection)

 

And you can filter whatever combination you want.

Hope this helps.

Cheers,

-Namish B

[manishgaikwad87]

Hi @NamishB ,

Thanks for your reply. But unfortunately the data in the query editor is in different format and hence was not able to try the below solution.

Attaching the images of the table and the outout which i am looking for. Hope this explains the concern in a better way:-

Table:

Output:

 

Regards,

Manish.

 

[v-easonf-msft · Accepted solution]

Hi, @manishgaikwad87 
First, you need to create two calculated tables as below:

Has Product = DISTINCT('Customer inventory'[Product])

Does Not Have Product = DISTINCT('Customer inventory'[Product])

Then create two visual control measure as below:

Condition1_has product =
VAR flag =
    IF (
        CALCULATE (
            MAX ( 'Customer inventory'[Price] ),
            FILTER (
                'Customer inventory',
                'Customer inventory'[Product] IN VALUES ( 'Has Product'[Product] )
                    = BLANK ()
            )
        ),
        0,
        1
    )
RETURN
    IF ( ISFILTERED ( 'Has Product' ), flag, 1 )

Condition2_does not have product =
VAR flag =
    IF (
        CALCULATE (
            MAX ( 'Customer inventory'[Price] ),
            FILTER (
                'Customer inventory',
                'Customer inventory'[Product] IN VALUES ( 'Does Not Have Product'[Product] )
                    <> BLANK ()
            )
        ),
        0,
        1
    )
RETURN
    IF ( ISFILTERED ( 'Does Not Have Product'[Product] ), flag, 1 )

Just drag them to visual filter pane,you will get the result as below:

Check attached pbix file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NamishB · Accepted solution]

Hi @manishgaikwad87 - there are many ways to do this. One of way as below-

Have 2 extra columns (HasINT) and (HasCHB)

formula for these would be something like- 

= Table.AddColumn(#"Changed Type", "HasInt", each if [INT] = null then "Does Not have" else "Has")

= Table.AddColumn(#"Added Custom", "HasCHB", each if [CHB] = null then "Does Not have" else "Has")

table in query editor looks like-

In Report panel-

Add 2 slicers one for each HasInt and HasCHB (Make sure in Format> edit Interactions> Stop filtering each slicers based on selection)

 

And you can filter whatever combination you want.

Hope this helps.

Cheers,

-Namish B