Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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).
@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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
64 | |
63 | |
56 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |