[SOLVED] Filter on 3 True/False Columns

Filter on 3 True/False Columns

I am trying to combine 3 true/false columns in a visual slicer where selecting the column name filters all where that column is true.

I want the user to be able to pick (single-select): - Column1 - Column2 - Column3

The page should be filtered on all true in column.

My Data

Column1| Column2| Column3|
true   | true   | false  |
false  | true   | false  |
false  | false  | true   |

The data overlaps so I am unable to create a calculate column using if statements. Im really stuck on this any help pointing me in the right direction is appreciated.

Solution

You have not specified if you want it in M or DAX. Solution in M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgkKdVXSUXJz9AlG0LE6GBJgLkgcRYB0cfItQOYjm4PqdCw24DCIkMW4bSDfJGL9gMPTxIYRqjGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type logical}, {"col2", type logical}, {"col3", type logical}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "AllTrue_M", each if [col1] = false then false else if [col2] = false then false else if [col3] = false then false else true)
in
    #"Added Conditional Column"

Or add calculated column in DAX:

CalculatedColumn = IF(
    'Table'[col1]=TRUE() && 'Table'[col2]=TRUE() && 'Table'[col3]=TRUE()
    , "ALL TRUE"
    , "NOT ALL TRUE"
    )

Alternatively, check for other combinations:

CalculatedColumn = SWITCH(TRUE(), 
'Table'[col1]=TRUE() && 'Table'[col2]=TRUE() && 'Table'[col3]=TRUE(), "ALL TRUE", 
'Table'[col1]=FALSE() && 'Table'[col2]=FALSE() && 'Table'[col3]=FALSE(), "ALL FALSE",
"MIXED")

Indeed it is interesting problem how to make check for multiple condition. Sadly AND function in DAX accepts only two arguments (conditions). You can check multiple conditions with "&&" operator.

https://learn.microsoft.com/en-us/dax/dax-operator-reference#logical-operators