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

     

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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks parry2k !