Return a Table Statement from IF-condition (for TREATAS)

[rks]

Hi folks,

I receive an error message:

The TREATAS function expects a table expression for argument '1', but a string or numeric expression was used.

I have the following measure:

VAR __TableVariable=
    IF(
        ISFILTERED( 'Product'[Color] ),
        VALUES( 'Product'[Color] ),
        { "#all_colors" }
    )
RETURN
    CALCULATE(
        SUM( 'Product Forecast'[Value] ),
        TREATAS ( __TableVariable , 'Product Forecast'[Color] )
)

 The use cases:
I have a sales table related to the product table ('product'). The product table contains a column called "color".

I also have a forecast table that holds data for a given product color. I want to create data lineage between the product-table and the forecast color if the product table is filted. If it is not filtered I want to use the default member "#all_colors". This is because the sum of the forecast value over all rows is not equal the the prediction for "#all_colors". 

The error message suggests that the variable is no table expression, however both branches (if and else) of the IF-function are valid table expressions.

I tried to reproduce the behavior on dax.do: https://dax.do/ravDLEMzBhp9Mr/

Help to avoid the error is much appreciated.

Thank you!

Konstantin

[leomureb · Accepted solution]

Hi,

I was having the same issue as you and adding the variable inside {} in the TREATAS function solved my issue.

Try this:

VAR __TableVariable=
    IF(
        ISFILTERED( 'Product'[Color] ),
        VALUES( 'Product'[Color] ),
        { "#all_colors" }
    )
RETURN
    CALCULATE(
        SUM( 'Product Forecast'[Value] ),
        TREATAS ( {__TableVariable} , 'Product Forecast'[Color] )
)

[rks]

Hi @devesh_gupta  thank you for the reply!

Changing the measure results in the same behavior.

However, the businesslogic is also wrong, because I don't want to remove the filters from the column, but instead use the value "#all_colors" which holds the forecast data if no products are selected (which is different from the sum of all values).

[devesh_gupta]

@rks Try modifying your measure as follow to make your __TableVariable consistently a table. Try if it works for you:

VAR __TableVariable=
    IF(
        ISFILTERED( 'Product'[Color] ),
        VALUES( 'Product'[Color] ),
        ALL( 'Product'[Color] )
    )
RETURN
    CALCULATE(
        SUM( 'Product Forecast'[Value] ),
        TREATAS ( __TableVariable , 'Product Forecast'[Color] )
)

If you find this insightful, please provide a Kudo and accept this as a solution.