Filter Table based on multiple True/False columns

[Anonymous]

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

[parry2k · Accepted 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

[Anonymous]

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?

[parry2k]

@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. 

[Anonymous]

Thanks @parry2k !