Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am trying to create a filter table Z that manages four boolean columns in another table X. For example, if the filter is set to "A", then it filters out all the FALSE statements in column "A" in Table X. How can I approach this without using an unpivot on my table X?
Filter Table Z
A |
B |
C |
D |
Table X
ID | A | B | C | D |
1 | TRUE | FALSE | TRUE | FALSE |
2 | TRUE | FALSE | TRUE | TRUE |
3 | FALSE | FALSE | TRUE | FALSE |
4 | FALSE | FALSE | TRUE | TRUE |
5 | TRUE | TRUE | TRUE | FALSE |
6 | FALSE | TRUE | FALSE | TRUE |
7 | FALSE | FALSE | FALSE | FALSE |
8 | TRUE | TRUE | FALSE | TRUE |
9 | FALSE | FALSE | FALSE | FALSE |
10 | FALSE | TRUE | FALSE | TRUE |
11 | FALSE | FALSE | FALSE | TRUE |
12 | TRUE | TRUE | FALSE | FALSE |
13 | FALSE | FALSE | TRUE | FALSE |
14 | TRUE | TRUE | TRUE | FALSE |
15 | FALSE | FALSE | FALSE | FALSE |
16 | TRUE | TRUE | TRUE | FALSE |
17 | TRUE | FALSE | FALSE | TRUE |
18 | TRUE | TRUE | FALSE | TRUE |
19 | TRUE | TRUE | FALSE | FALSE |
20 | TRUE | FALSE | TRUE | FALSE |
Filter Table Z set to A
A |
Table X filtered based on column A being TRUE
ID | A | B | C | D |
1 | TRUE | FALSE | TRUE | FALSE |
2 | TRUE | FALSE | TRUE | TRUE |
5 | TRUE | TRUE | TRUE | FALSE |
8 | TRUE | TRUE | FALSE | TRUE |
12 | TRUE | TRUE | FALSE | FALSE |
14 | TRUE | TRUE | TRUE | FALSE |
16 | TRUE | TRUE | TRUE | FALSE |
17 | TRUE | FALSE | FALSE | TRUE |
18 | TRUE | TRUE | FALSE | TRUE |
19 | TRUE | TRUE | FALSE | FALSE |
20 | TRUE | FALSE | TRUE | FALSE |
Many thanks,
Jason
Solved! Go to Solution.
@Anonymous add a measure in your table as below:
What to Filter? = VAR __filter = SELECTEDVALUE( 'Filter Table'[Filter] ) VAR __Isfilter = SWITCH( __filter, "A", SELECTEDVALUE( table3[A] ) = TRUE(), "B", SELECTEDVALUE( table3[B] ) = TRUE(), "C", SELECTEDVALUE( table3[C] ) = TRUE(), "D", SELECTEDVALUE( table3[D] ) = TRUE() ) RETURN IF( __Isfilter = TRUE(), 1, 0 )
on your table visual, add visual level filter and select "What to filter?" is 1 as shown below and based on your selection in slicer, it will filter table accordingly
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous add a measure in your table as below:
What to Filter? = VAR __filter = SELECTEDVALUE( 'Filter Table'[Filter] ) VAR __Isfilter = SWITCH( __filter, "A", SELECTEDVALUE( table3[A] ) = TRUE(), "B", SELECTEDVALUE( table3[B] ) = TRUE(), "C", SELECTEDVALUE( table3[C] ) = TRUE(), "D", SELECTEDVALUE( table3[D] ) = TRUE() ) RETURN IF( __Isfilter = TRUE(), 1, 0 )
on your table visual, add visual level filter and select "What to filter?" is 1 as shown below and based on your selection in slicer, it will filter table accordingly
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks @parry2k !
I took your example and reduced it (to see if I actually understood your work). I'm new to DAX, so I am not sure if there is a best practice for writing DAX. This is how I reduced it:
What is Filter? 3 = IF( SWITCH( SELECTEDVALUE( 'Filter Table'[Filter] ), "A", SELECTEDVALUE( table3[A] ), "B", SELECTEDVALUE( table3[B] ), "C", SELECTEDVALUE( table3[C] ), "D", SELECTEDVALUE( table3[D] ) ) = TRUE(), 1, 0)
1. Why do we need the '= TRUE()' statement after the SELECTEDVALUE('table3'[A]) statement?
2. Is it best practice to define statements in separate VARs?
3. Is it best practice to use RETURNIF vs. wrapping the whole statement in an IF statement?
@Anonymous your dax expression is also good but best practice and experts recommend to break it into pieces, it is much easier to debug, easy to read and easy to make the changes in future if someone else is looking at the expression.
Again your expression would work but bit difficult to ready at first go.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |