Nested IFs and ANDs in DAX measure

[Anonymous]

Hi all,

Could someone please advise me on how I can translate this long nested IFs and ANDs formula from standard Excel (where it works perfectly) onto PBI DAX measure?:

=+IF(AND([@[Projekt-Nr.]]>9999;[@[Externe/Interne]]="Interne");"Interne - kein Zeitprojekt";

IF(AND([@[Projekt-Nr.]]>9999;[@[Externe/Interne]]="Externe");"Externe - kein Zeitprojekt";IF(ISNUMBER(SEARCH("reise";[@Aufgabe]));"Reisezeit";

IF(AND([@[Projekt-Name]]<>"";IF(ISNUMBER(SEARCH("supp";[@[Projekt-Name]]));"Support";"Not Support")="Not Support";[@[Pauschal/Aufwand]]="Pauschal";[@[Externe/Interne]]="Externe";[@[Eröffnung Budget]]-[@Verrechenbar]>=0);"Pauschal within budget";

IF(AND(IF(ISNUMBER(SEARCH("supp";[@[Projekt-Name]]));"Support";"Not Support")="Support");"Support";

IF(AND([@[Pauschal/Aufwand]]="Pauschal";[@[Externe/Interne]]="Externe";[@Budgetzeit]=0);"Pauschal set up with 0 budget";

IF(AND(IF(ISNUMBER(SEARCH("supp";[@[Projekt-Name]]));"Support";"Not Support")="Support");"Support";

IF(AND([@[Pauschal/Aufwand]]="Pauschal";[@[Externe/Interne]]="Externe";[@[Eröffnung Budget]]=0);"Pauschal started the period with 0 budget";

IF(AND(IF(ISNUMBER(SEARCH("supp";[@[Projekt-Name]]));"Support";"Not Support")="Support");"Support";

IF(AND([@[Pauschal/Aufwand]]="Pauschal";[@[Externe/Interne]]="Externe";[@[Eröffnung Budget]]<0);"Pauschal started the period with budget in minus";

IF(AND([@[Pauschal/Aufwand]]="Aufwand";[@[Externe/Interne]]="Externe";[@[Eröffnung Budget]]-[@Verrechenbar]>=0);"Aufwand within budget";

IF(AND([@[Pauschal/Aufwand]]="Aufwand";[@[Externe/Interne]]="Externe";[@[Eröffnung Budget]]-[@Verrechenbar]<0);"Aufwand overbudget";IF([@[Externe/Interne]]="Interne";"Interne";IF([@[Eröffnung Budget]]-[@Verrechenbar]<0;"Pauschal exceeded budget during period";

IF(AND([@[Eröffnung Budget]]=0;[@Verrechenbar]=0);"No Budget and Verrechenbar";"Strange Result")))))))))))))))

I tried (even utilising SWITCH), but couldn't get it to work.

Looking forward to your feedback.

Thanks.

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Dax is very close to excel formula,but remember to add "MAX" or "SELECTEDVALUE" before fields if you wanna create measures.

Such as :

Back Color = 
SWITCH(
    SELECTEDVALUE(DimCustomer[EnglishEducation]),
    "Bachelors","Green",
    "High School","Red",
    "Partial High School","Tan",
    "Graduate Degree","Yellow",
    "White"
)

Or 

Sales YTDOY :=
CALCULATE (
    SUMX (
        VALUES ( 'Date'[Calendar Year Month Number] ),
        VAR CurrentSales = [Sales Amount]
        VAR PreviousSales = [Sales LY]
        RETURN
            IF (
                AND (
                    CurrentSales <> 0,
                    PreviousSales <> 0
                ),
                CurrentSales - PreviousSales
            )
    ),
    DATESYTD ( 'Date'[Date] )
)

Check the reference below:

https://radacad.com/write-conditional-statement-using-switch-in-dax-and-power-bi

https://www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Vera_33]

Hi @Anonymous 

Provide some sample data along with expected results in a format which people can copy to help understand the logic