Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter Data across an id column using logic with multiple slicers

Hi All, 

I am wondering if this is possible.
I have some product data associated with Ids and the goal is to let users filter for ids where there is a corresponding product mix across those ids.

Sample Data

IdProduct
1a
1b
1e
2a
2b
2c
3a
3c
3e
4d
4e
4f
5g
5h
5i

 

I would like to utilize two slicers the user can pick ids from to filter the data to return ids where any value from slicer 1 is found and any value from slicer 2 is found. Example here would be Id has any product from slicer 1 AND any product from slicer 2

 

has any ofand has any of 
slicer1slicer2
ae
b 
c 
d 

 

to return something like this

 

IdHas Product Mix
1yes
3yes
4yes


Any Help is appreciated and thank you for your time.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please create 2 new tables:

    Table 2 = DISTINCT('Table'[Product])
    Table 3 = DISTINCT('Table'[Product])

    Then use them to create two slicers.

    Then create a new measure:

    Measure = 
VAR _table1 = CALCULATETABLE(VALUES('Table'[Id]),'Table'[Product] IN ALLSELECTED('Table 2'[Product]))
VAR _table2 = CALCULATETABLE(VALUES('Table'[Id]),'Table'[Product] IN ALLSELECTED('Table 3'[Product]))
VAR _table3 = INTERSECT(_table1,_table2)
VAR _result = IF(MAX('Table'[Id]) IN _table3,"YES")
RETURN
_result

    Result;

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please create 2 new tables:

    Table 2 = DISTINCT('Table'[Product])
    Table 3 = DISTINCT('Table'[Product])

    Then use them to create two slicers.

    Then create a new measure:

    Measure = 
VAR _table1 = CALCULATETABLE(VALUES('Table'[Id]),'Table'[Product] IN ALLSELECTED('Table 2'[Product]))
VAR _table2 = CALCULATETABLE(VALUES('Table'[Id]),'Table'[Product] IN ALLSELECTED('Table 3'[Product]))
VAR _table3 = INTERSECT(_table1,_table2)
VAR _result = IF(MAX('Table'[Id]) IN _table3,"YES")
RETURN
_result

    Result;

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data