Forum Discussion
Dynamic Filter based on another Filter
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.
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.CheersCheenuSing
6 Replies
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")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
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.
