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:

Mark_Timson's avatar
Mark_Timson
Helper I
7 years ago

Hi Is there anyway to do this with more than one table and column? 

 

I have 

 

IF Table.Col = "Y" THEN SUM(Table1.Col) ELSE IF Table.Col = "N" THEN SUM(Table2.Col) ELSE 0.

 

I have tried to keep the above simple.