Forum Discussion

yaya1974's avatar
yaya1974
Helper III
2 years ago
Solved

Nested IF or Filter

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!

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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.

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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's avatar
      yaya1974
      Helper III
      2 years ago

      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's avatar
        rajendraongole1
        Super User
        2 years ago

        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.