Forum Discussion
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.
thanks!
Hi Anonymous,
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
2 Replies
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
Hi Anonymous,
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
