Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
JenAbe
Frequent Visitor

CALCULATETABLE and TREATAS?

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.

1 REPLY 1
Mrxiang
Helper II
Helper II

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors