Search code examples
if-statementssasdaxtabular

SSAS Tabular DAX using of IF Statement

I have this query written in SQL:

SELECT
   CustomerId,
   CustomerType,
   CASE WHEN CustomerStatus = 'VIP' 
        THEN CustomerDiscountType
        ELSE NULL
   END AS CustomerDiscountType
FROM Customer

And I would like to write this in DAX query: I know that I can write like this:

EVALUATE
(
    SUMMARIZE
    (
        'Customer',
        'Customer'[CustomerId],
        'Customer'[Type],
        'Customer'[DiscountType],
        "Customer VIP", IF('Customer'[Status] = "VIP", 'Customer'[DiscountType], BLANK())
    )
)

But when I write if condition I need include attribute 'Customer'[DiscountType] in that query too, but I would like to write the column name of that IF statement "DiscountType", but it isn't possible for me like below.

EVALUATE
(
    SUMMARIZE
    (
        'Customer',
        'Customer'[CustomerId],
        'Customer'[Type],
        "DiscountType", IF('Customer'[Status] = "VIP", 'Customer'[DiscountType], BLANK())
    )
)

It failed with this error because of existing DiscountType column: Function 'SUMMARIZE' cannot add column [DiscountType] since it already exists.

Solution

  • Instead of using SUMMARIZE you can use SELECTCOLUMNS function. I think the error is caused because the IF function returns DiscountType column and it already exists in your Customer table. Also I am unsure if SUMMARIZE works without any aggregation like used in your DAX expression.

    Try this expression:

    EVALUATE
 (
    SELECTCOLUMNS (
        'Customer',
        "CustomerId", 'Customer'[CustomerId],
        "Type", 'Customer'[Type],
        "DiscountType", IF ( 'Customer'[Status] = "VIP", 'Customer'[DiscountType], BLANK () )
    )
)

    UPDATE: OP tells via comments the version used is SSAS 2014 which doesn't support SELECTCOLUMNS function.

    You can use a mix of SUMMARIZE and ADDCOLUMNS functions to get the expected result.

    EVALUATE
 (
    SUMMARIZE (
        ADDCOLUMNS (
            ADDCOLUMNS (
                DISTINCT ( Customer[CustomerID] ),
                "Type", CALCULATE ( VALUES ( Customer[Type] ) ),
                "Status", CALCULATE ( VALUES ( Customer[Status] ) ),
                "DiscountType1", CALCULATE ( VALUES ( Customer[DiscountType] ) )
            ),
            "DiscountType", IF ( [Status] = "VIP", [DiscountType1], BLANK () )
        ),
        [CustomerId],
        [Type],
        [DiscountType]
    )
)

    It is not tested but should work, let me know if this helps for you.