Forum Discussion

miarismendez's avatar
miarismendez
Regular Visitor
4 years ago
Solved

Comparing 3 columns in the same table for a return result

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! 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      miarismendez
      Regular Visitor
      4 years ago

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

        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.