CALCULATETABLE and TREATAS?

JenAbe · ‎05-24-2023

I am playing with a dax query that uses a subset of data from an existing power bi dataset table. I have done this before successfully but this particular use case has a bit of a twist.

The results of the subset created from that calculate table need to use a treatas to force all the returned rows to use a specific constant in place of an existing field value that has a relationship to a dimension table - thus forcing all the rows returned in the subset to reference the same dimension value.

I know that sounds wierd...and I can do it in SQL and m, but I wanted to see if DAX can do it too.

Here is a super simplified, generic version of what I did:

VAR Base = CALCULATETABLE
(SUMMARIZE
( 'MyTable', the columns,....),
some filters,...
)

The I add the values I am going to use in the subsequent returned result

VAR Add_Stuff =
ADDCOLUMNS( Base,
"FirstValue", SUM( 'MyTable'[Value] ),
"Second", SUM( 'MyTable'[AnotherValue] )
)

Finally I create the result I want to return (which will be referenced in other dax measures)

VAR Result = SUMX( Add_Stuff, [FirstValue])-SumX(Add_Stuff,[SecondValue])

Here is the tricky part - MyTable which is the source for this subset has an established relationship with a dimension table. Lets call that table DimType. I want this subset to ignore the current value in the MyTable FK and intead force it to use the constant 123 for all rows.

TREATAS ({123}, DimTypeFK)

I thought this was where the TREATAS command would come in handy but I am not sure how to do or where to put it or if it will even work. I don't get an error in the DAX syntax per say but I am not getting the results I am expecting either.

Am I approaching this problem the correct way?

Any help appreciated. I hope I am merely missing something super obvious.

Mrxiang · ‎05-24-2023

Based on your description, it seems like you are trying to use the TREATAS function in DAX to force all the rows in a subset to reference the same dimension value. However, you are not sure how to use the function or where to put it in your DAX code.

First, let's review what the TREATAS function does in DAX. This function allows you to temporarily override the filter context of a column or table with a new set of values. In your case, you want to override the filter context of the MyTable FK column with a constant value of 123 for all rows in the subset.

To use the TREATAS function in your DAX code, you can add it as a filter argument to your CALCULATETABLE function. Here's an example of how you can modify your code to include the TREATAS function:

VAR Base = CALCULATETABLE(
    SUMMARIZE('MyTable', the columns, ...),
    some filters,
    TREATAS({123}, 'MyTable'[DimTypeFK])
)

VAR Add_Stuff = ADDCOLUMNS(
    Base,
    "FirstValue", SUM('MyTable'[Value]),
    "SecondValue", SUM('MyTable'[AnotherValue])
)

VAR Result = SUMX(Add_Stuff, [FirstValue]) - SUMX(Add_Stuff, [SecondValue])
In this modified code, we added the TREATAS function as a filter argument to the CALCULATETABLE function. We passed in a set of values ({123}) and the column we want to override the filter context for ('MyTable'[DimTypeFK]). This will force all the rows in the subset to use the constant value of 123 for the DimTypeFK column.

I hope this helps you solve your problem. Let me know if you have any further questions or concerns.