Forum Discussion

MichaelDWebb's avatar
MichaelDWebb
Frequent Visitor
7 years ago
Solved

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 number...
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
MichaelDWebb
Frequent Visitor
7 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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
     
     
    • MichaelDWebb's avatar
      MichaelDWebb
      Frequent Visitor
      7 years ago

      Hi Anonymous,

       

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

       

      Thansk,

      Mike

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

       

      Regards

      Anonymous