Forum Discussion

Kaalbartje2's avatar
Kaalbartje2
Frequent Visitor
3 years ago
Solved

Nested IF in Dax or M

Hello,

 

I searched but I couldn't find the solution what my problem is.

I have different department in a column and I have a specialism in another.

I want to specify when a specialism is wrong in a department.

 

So for

Department A it is specialism "cardio" that is correct, others are False

Department B it is specialsm "cardio" and "pulmo" that is correct, other are False

But "pulmo" is also correct on department C and for C als, "KNO" and "anest"

 

the solution can be written in M or in DAX. I treid both.

Please help 🙂

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Kaalbartje2 ,

    Please have a try.

    Create a column.

    column = 
IF (
    'table'[department] = "Department A"
        && 'table'[specialism] = "cardio",
    TRUE (),
    IF (
        'table'[department] = "Department B"
            && ( 'table'[specialism] = "cardio"
            || 'table'[specialism] = "pulmo" ),
        TRUE (),
        IF (
            'table'[department] = "Department C"
                && ( 'table'[specialism] = "pulmo"
                || 'table'[specialism] = "KNO"
                || 'table'[specialism] = "anest" ),
            TRUE (),
            FALSE ()
        )
    )
)

    If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Kaalbartje2 ,

    Please have a try.

    Create a column.

    column = 
IF (
    'table'[department] = "Department A"
        && 'table'[specialism] = "cardio",
    TRUE (),
    IF (
        'table'[department] = "Department B"
            && ( 'table'[specialism] = "cardio"
            || 'table'[specialism] = "pulmo" ),
        TRUE (),
        IF (
            'table'[department] = "Department C"
                && ( 'table'[specialism] = "pulmo"
                || 'table'[specialism] = "KNO"
                || 'table'[specialism] = "anest" ),
            TRUE (),
            FALSE ()
        )
    )
)

    If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

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

    • Kaalbartje2's avatar
      Kaalbartje2
      Frequent Visitor
      3 years ago

      Hi v-polly-msft,

       

      Looking good, I'll give it a try on my real dataset which it's more complex. But when I see the result on department C I have really good hope.

       

      I'll let you know! Tnx!!

      • Kaalbartje2's avatar
        Kaalbartje2
        Frequent Visitor
        3 years ago

        Anonymous  It works perfectly. Tnx. I marked it as the solution.

         

    • Kaalbartje2's avatar
      Kaalbartje2
      Frequent Visitor
      3 years ago

      However when working with the dataset I noticed an other issue. Over the years the parameters can change. So the specialisme that belongs at a department changes.

      In 2019 it's different than 2021 and further.

      Can we make that decision also in the code?

  • latimeria's avatar
    latimeria
    Icon for Solution Specialist rankSolution Specialist
    3 years ago

    hi Kaalbartje2 ,

    I don't understand what you want.

    Do you want to build a table with valid departments / specialsms and check whether it is correct in your transactions?

    • Kaalbartje2's avatar
      Kaalbartje2
      Frequent Visitor
      3 years ago

      Hello,

       

      Tnx for looking to my question.

      I want to make a new column with true or false. When the specialism doesn't match the department it's a false. A true when they do match.