Dynamic Filter based on another Filter

[MichaelDWebb]

I have a report that has a table that shows open purchase orders. Purchaing manager then has a set of filters that shows problematic line items. I would like to take the list of purchase order numbers on this fitlered table, then filter the table (or a separate table), for all lines not equal to these purchase order numbers. Is this possible to creat a dynamic filter like this?

PS: I can't just reverse the filter criteria. Since if 1 line item has a problem, all line items for that purchase order need to be frozen.

[CheenuSing]

Hi @MichaelDWebb 

Based on my understanding, I did the following

1. Created a table of POS - that contains only the PO numbers from the original Data table.

2. Disable any relationship between this and Data table under the modelling ManageRelationship

3. Created a slicer with POS[PO] and not from Data[PO]

4. Created a measure    

Check  =

IF (ISFILTERED(POS[PO]) ,

    IF(VALUES(Data[PO]) = MAX(POS[PO]), "Y","N")

[CheenuSing]

Hi @MichaelDWebb 

I inadvertently pressed the post key last time.

Based on my understanding, I did the following

1. Created a table of POS - that contains only the PO numbers from the original Data table.

2. Disable any relationship between this and Data table under the modelling ManageRelationship

3. Created a slicer with POS[PO] and not from Data[PO]

4. Created a measure    

Check  =

IF (ISFILTERED(POS[PO]) ,

    IF(VALUES(Data[PO]) = MAX(POS[PO]), "Y","N")

What this does is if the PO number is filtered then it sets it as Y else as N.

5. Created table visual with all columns from Data table. In the visual filters added the Check measure built above and in the filtering condition set it to Y.

What this will show is the selected PO with all line items in that PO.

6. Created another table visual with all columns from Data table. In the visual filters added the Check measure built above and in the filtering condition set it to N.

What this will show is the all PO with all line items in those  PO other than the selected one.

Sample output

Slicer on the left from POS[PO], The table next to that is as per step 5 . Then the next one as per step 6.

The bottom table is the full list of Data table.

I hope this works for you.

Cheers

CheenuSing

[MichaelDWebb]

Thanks for this tutorial, opened up some new ideas for me. I was able to make this work, per your instructions. Unfortuanately, I did not clarify that I need to be able to select multiple values on the slicer.

[CheenuSing · Accepted solution]

Hi @MichaelDWebb 

No worries. Just change the Check measure as follows

Check =

IF (ISFILTERED(POS[PO]) ,

    IF(VALUES(Data[PO]) in Values((POS[PO])), "Y","N"),"N"

)

This will work even if there are mutiple selection on POS[PO].

I have added one more "N" to tell that if none is selected the entire Data Table will be displayed.

Cheers

CheenuSing

[Anonymous]

Hello, can I do this with categorical variables, instead of 102..101 I have Product_1, product_2...

Regards

@CheenuSing

[MichaelDWebb]

Hi @CheenuSing,

Thank you, this worked perfectly. Is there a way to auto check all of the items on the slicer?

Thansk,

Mike