Comparing 3 columns in the same table for a return result

[miarismendez]

I have three columns of data that if certain combinations are meet then its true, otherwise false. These are text values and all the examples I have seen are for number values.

This is my report and these are the combinations I am trying write a DAX formula.

If the row has any of the following combinations, then the return should be true. It the row does not, then it returns false.

Session Category	Sample Type	Sample Method	True/False
Area/Background	Chemical	Area	True
Area/Background	Chemical	Background	True
Area/Background	NORM	Area	True
Area/Background	NORM	Background	True
Area/Background	Area Noise	Area	True
Personal	Chemical	Personal	True
Personal	Personal Noise	Personal	True

I tried If and switch but nothing seem to work right.

Please help! 

[v-rzhou-msft · Accepted solution]

Hi @miarismendez ,

There should be a table with all True combinations.

Create a calculated column in your data table.

True/False = 
VAR _COMBINELIST =
    SELECTCOLUMNS (
        Combinations,
        "Combine",
            COMBINEVALUES (
                "-",
                Combinations[Session Category],
                Combinations[Sample Type],
                Combinations[Sample Method]
            )
    )
VAR _COMBINEVALUE =
    COMBINEVALUES (
        "-",
        'Table'[Session Category],
        'Table'[Sample Type],
        'Table'[Sample Method]
    )
RETURN
    IF ( _COMBINEVALUE IN _COMBINELIST, TRUE (), FALSE () )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[miarismendez]

I was able to make it work but I wondered is creating a calculated column the only way? Is there a measure that would do the same thing?

Also, is a separate table with the combinations always needed in these types of comparisons?

I am just wondering since there seems to be different ways and for some people still learning Power BI I want to make sure we use something they can remember easily.

Either way thank you!!!

[lbendlin]

Calculated columns are preferable when the result is immutable (not influenced by users' filter choices).

Measures are required if the result can be impacted by users'  filter choices.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. 

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523