Nested IF in Dax or M

[Kaalbartje2]

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 🙂

[Anonymous · Accepted solution]

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]

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?

[Kaalbartje2]

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]

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

[latimeria]

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]

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.