Forum Discussion

cdawidow's avatar
cdawidow
Helper III
6 years ago

Nested If Statements

Hi guys, I have 4 measures each recieving a final alpha grade (A,B,C,D).  I want to use an IF statement to essentially select specific outcomes for these measures that would match or equal a select outcome.  For reference my measures are as follows: 

Customer NameCustomer Sales RankLoyalty MeasureMargin Finalcts
xxxAAAC
yyyyAAAB
vcvABDD

 

So for a good customer, they need to have either an A OR B in both sales, loyalty, margin, but a c or d scoring in cts.

As for a potential good customer, they need to have A OR B in margin, but C & D in the rest of the factors.

 

I have used the following measure but I still get stuck.  

IF(SALES RANK = "A" || SALS RANK "B" && MARGIN FINAL = "A"||MARGIN FINAL = B ETC......

 

I have used the OR operators and && operators but I still sometimes do not get the correct grouping.  Any ideas?

DAX
power bi

3 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    Hi cdawidow 

     

    You get the wrong answers because your are not grouping your 'or' statements together with paranthesis.

     

    You can also try to rewrite your measure using the 'IN'-function:

    IF([SALES RANK] in ("A","B") && [MARGIN FINAL] in ("A","B") && [LOYALTY] in ("A","B") && [FINALCTS] in ("C","D")

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too. 

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    cdawidow 

    Try this measure and replace the column names with your measure names:

    Measure = 

IF(
    (
        MAX(DATA1[Customer Sales Rank]) IN {"A","B"} && 
        MAX(DATA1[Loyalty Measure]) IN {"A","B"} && 
        MAX(DATA1[Margin Final]) IN {"A","B"} 
    ) &&
    (
        MAX(DATA1[cts]) IN {"A","B","C","D"}
    ), 
    "GOOD",
    IF(
        (
            MAX(DATA1[Customer Sales Rank]) IN {"A","B"} && 
            MAX(DATA1[Loyalty Measure]) IN {"A","B"} 
        ) &&
        (
            MAX(DATA1[Margin Final]) IN {"A","B","C","D"} && 
            MAX(DATA1[cts]) IN {"A","B","C","D"}
        ),
    "POTENTIAL"
    )
)

     

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube, LinkedIn

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    cdawidow , Try like

    switch( true(),
    [Customer Sales] in {"A","B"} && [Rank Loyalty] in {"A","B"} && [Measure Margin] in {"A","B"} && [Final cts] in {"C","D"} , "Good",
    "Bad"
    )

     

    or a measure like

    maxx(summarize( Table, Table[Customer Name], "Rank" ,
    switch( true(),
    [Customer Sales] in {"A","B"} && [Rank Loyalty] in {"A","B"} && [Measure Margin] in {"A","B"} && [Final cts] in {"C","D"} , "Good",
    "Bad"
    )
    )