cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mford Frequent Visitor
Frequent Visitor

Use filtered table values as second OR filter

I have a table with the fields id_division, Project, and Fleet.  I have 3 other tables where users have specified what values of those 3 fields they would like to monitor.

 

I am trying to find a way to let a user pick their name in a Slicer, and have the table filter down to only the records they care about.  The desired values are to be treated as OR, not as AND.

 

2018-09-21_0950.png

 

thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Use filtered table values as second OR filter

Hi @mford,

 

That table containing fields id_division, Project, and Fleet should be unrelated with the other three tables. Then, please create below measures:

Measure1 = CONCATENATEX(ALLSELECTED(Table2),Table2[Div2trk],",")

Measure2 = CONCATENATEX(ALLSELECTED(Table3),Table3[PROJ2trk],",")

Measure3 = CONCATENATEX(ALLSELECTED(Table4),Table4[Fleet2trk],",")

flag1=IF(ISERROR(FIND(SELECTEDVALUE(Table1[id_division]),[Measure1])),0,1)

flag2=IF(ISERROR(FIND(SELECTEDVALUE(Table1[Project]),[Measure2])),0,1)

flag3=IF(ISERROR(FIND(SELECTEDVALUE(Table1[Fleet]),[Measure3])),0,1)

Measure4=IF([flag1]+[flag2]+[flag3]>0,1,0)

Choose the table visual that you want to show records, add [Measure4] to visual level filter, and set its value to 1.

 

Best regards,

Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
davehus Member
Member

Re: Use filtered table values as second OR filter

Hi, 

 

I've played around with this with random data and it could be worth a try. If you create a calculated column at query level with something like

 

 

= Table.AddColumn(#"Changed Type", "Flag", each if[id_division] = 68 or [Fleet] = CNH_US_FLT_2 or [Project] = "CT_INV_West" then 1 else 0)

 From here you could create a user table with a UID column of 1 for Marc, 2 for Another etc. and create a relationship between the flag. It should filter down to the require data.

2nd option would be to create a bookmark and add the flag at report level and put buttons on the page for each person. Alternatively, you could write the name into the calculated column instead of the numerical values.

 

HTH D

Community Support Team
Community Support Team

Re: Use filtered table values as second OR filter

Hi @mford,

 

That table containing fields id_division, Project, and Fleet should be unrelated with the other three tables. Then, please create below measures:

Measure1 = CONCATENATEX(ALLSELECTED(Table2),Table2[Div2trk],",")

Measure2 = CONCATENATEX(ALLSELECTED(Table3),Table3[PROJ2trk],",")

Measure3 = CONCATENATEX(ALLSELECTED(Table4),Table4[Fleet2trk],",")

flag1=IF(ISERROR(FIND(SELECTEDVALUE(Table1[id_division]),[Measure1])),0,1)

flag2=IF(ISERROR(FIND(SELECTEDVALUE(Table1[Project]),[Measure2])),0,1)

flag3=IF(ISERROR(FIND(SELECTEDVALUE(Table1[Fleet]),[Measure3])),0,1)

Measure4=IF([flag1]+[flag2]+[flag3]>0,1,0)

Choose the table visual that you want to show records, add [Measure4] to visual level filter, and set its value to 1.

 

Best regards,

Yuliana Gu

 

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