Nested IF or Filter

[yaya1974]

Multiplicaiton:  Dax code with filters, how to write code for a new column?

test = Weight1 * weight2 * Adj  ................filter by customer, filter by material, filter by channel (and maybe another filter as well) 

or IF statement if that would work better?  Unless there is something better, I am still learning all these DAX codes.

Thank you!

[rajendraongole1]

Hi @yaya1974 - Create calculated column using if and in your table

FilteredProduct =
IF (
AND (
'SalesData'[CustomerID] = "CustomerA", -- Replace with your filter criteria
'SalesData'[MaterialID] = "MaterialX", -- Replace with your filter criteria
'SalesData'[ChannelID] = "ChannelY" -- Replace with your filter criteria
),
'SalesData'[Weight1] * 'SalesData'[Weight2] * 'SalesData'[Adj],
BLANK() -- Returns blank if the filter criteria are not met
)

for better you can also try with switch statement

Hope it helps

[yaya1974]

Thank you!   As soon as I add the coma after the Material I get syntax error.  any idea why?   

Also I am not familar with Switch.

[rajendraongole1 · Accepted solution]

Hi @yaya1974 - can you try below one.

FilteredProduct =

IF (

    AND (

        AND (

            'iftest'[CustomerID] = "CustomerA",  

            'iftest'[MaterialID] = "MaterialX"

        ),

        'iftest'[ChannelID] = "ChannelY"

    ),

    'iftest'[Weight1] * 'iftest'[Weight2] * 'iftest'[Adj],

    BLANK()  -- Returns blank if the filter criteria are not met

)

Hope it works.

[yaya1974]

Thank you!!