Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter Table based on multiple True/False columns

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

IDABCD
1TRUEFALSETRUEFALSE
2TRUEFALSETRUETRUE
3FALSEFALSETRUEFALSE
4FALSEFALSETRUETRUE
5TRUETRUETRUEFALSE
6FALSETRUEFALSETRUE
7FALSEFALSEFALSEFALSE
8TRUETRUEFALSETRUE
9FALSEFALSEFALSEFALSE
10FALSETRUEFALSETRUE
11FALSEFALSEFALSETRUE
12TRUETRUEFALSEFALSE
13FALSEFALSETRUEFALSE
14TRUETRUETRUEFALSE
15FALSEFALSEFALSEFALSE
16TRUETRUETRUEFALSE
17TRUEFALSEFALSETRUE
18TRUETRUEFALSETRUE
19TRUETRUEFALSEFALSE
20TRUEFALSETRUEFALSE

 

Filter Table Z set to A

A

 

Table X filtered based on column A being TRUE

IDABCD
1TRUEFALSETRUEFALSE
2TRUEFALSETRUETRUE
5TRUETRUETRUEFALSE
8TRUETRUEFALSETRUE
12TRUETRUEFALSEFALSE
14TRUETRUETRUEFALSE
16TRUETRUETRUEFALSE
17TRUEFALSEFALSETRUE
18TRUETRUEFALSETRUE
19TRUETRUEFALSEFALSE
20TRUEFALSETRUEFALSE

 

Many thanks,

Jason

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    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

     

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        parry2k
        Super User
        7 years ago

        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.