Forum Discussion

rks's avatar
rks
Icon for Resolver II rankResolver II
2 years ago
Solved

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

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

Need Help
  • leomureb's avatar
    leomureb
    1 year ago

    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] )
)

3 Replies

  • leomureb's avatar
    leomureb
    Icon for Microsoft Employee rankMicrosoft Employee
    1 year ago

    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] )
)
  • devesh_gupta's avatar
    devesh_gupta
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    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.

  • rks's avatar
    rks
    Icon for Resolver II rankResolver II
    2 years ago

    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).