Forum Discussion

MWinter225's avatar
MWinter225
Advocate IV
9 years ago
Solved

DAX Measure with Nested IF Statements

Hey Everyone,   This is something that is relatively easy for me to figure out in other BI tools but I've had some issues with Power BI and DAX. Here is a sample table "Sales" :    Store Numbe...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    MWinter225If you do want Measures - these should work also! :smileyhappy:

    MEASURE 1

    Total Adj Sales ALT =
SUMX (
    'Table',
    IF (
        'Table'[Adjustment] = "b",
        'Table'[Sales] * 0.9,
        IF ( 'Table'[Adjustment] = "c", 'Table'[sales] * 0.5, 'Table'[Sales] )
    )
)

    MEASURE 2 - SWITCH is internally converted into nested IFs - one thing I really like is that its much easier to read and write

    Total Adj Sales ALT 2 =
SUMX (
    'Table',
    SWITCH (
        TRUE (),
        'Table'[Adjustment] = "b", 'Table'[Sales] * 0.9,
        'Table'[Adjustment] = "c", 'Table'[sales] * 0.5,
        'Table'[Sales]
    )
)

    Now you have 3 options which should all give you the same result!

     

    Good Luck! :smileyhappy:

Sean's avatar
Sean
Community Champion
9 years ago

MWinter225If you do want Measures - these should work also! :smileyhappy:

MEASURE 1

Total Adj Sales ALT =
SUMX (
    'Table',
    IF (
        'Table'[Adjustment] = "b",
        'Table'[Sales] * 0.9,
        IF ( 'Table'[Adjustment] = "c", 'Table'[sales] * 0.5, 'Table'[Sales] )
    )
)

MEASURE 2 - SWITCH is internally converted into nested IFs - one thing I really like is that its much easier to read and write

Total Adj Sales ALT 2 =
SUMX (
    'Table',
    SWITCH (
        TRUE (),
        'Table'[Adjustment] = "b", 'Table'[Sales] * 0.9,
        'Table'[Adjustment] = "c", 'Table'[sales] * 0.5,
        'Table'[Sales]
    )
)

Now you have 3 options which should all give you the same result!

 

Good Luck! :smileyhappy:

Kayak6000's avatar
Kayak6000
Frequent Visitor
8 years ago

hello all,

that is really useful but i have encountered a further issues...

 

tried the switch function and I managed to get it working or at least syntactically correct, but the figures were calculating incorrectly.

 

So I tried the If function as per Option 2   and again all works but , although correctly calculating at a 'base' level, what it is doing is adding the percentages upwards (if that makes sense)

as the calc moves ‘up’ the visual to a higher level view,  it starts adding percentages giving me the total of 526.94% when it should be 105.53%

 

HN

1876246.84

-1785153.62

 

25.26%

105.10%

SH

658942.06

-631713.39

 

8.87%

104.31%

SH

1924556.23

-1824569.79

 

25.91%

105.48%

FN

1793206.98

-1685529.97

 

24.14%

106.39%

SF

1585047.95

-1500208.79

 

21.34%

105.66%

 

7838000.06

-7427175.56

 

105.53%

526.94%

 

 

 

 

 

 

 

 

I have looked the default summarisation and that is set to “don’t summarize” , in the visual itself when I right clicked, this is set as “show value as’ > “no calculation”

 

in essenec it shoud be adding up column 2 , adding up column 3 and then dixiding column 2 / column 3  the sums i have shown i  bold at the bottom (ps although negative I multiply the result by -1 to give me the positive % so ignore the negatives) and in fact it is because of these negatives that i needed the Switch or If  functions.

 

So am at a bit of a dead end… any ideas ?

 

thanks peeps