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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gvg
Post Prodigy
Post Prodigy

Unexpected return from TREATAS function

Hello,

I have been experimenting with TREATAS function and got stuck at some stage when odd results appeared.

I have this Sales table and disconnected ManagerTable:

 

Sales						            ManagerTable	
Date	Manager	Amount	ProductID		  FldX	Mgr1
-----------------------------------       ------------
2018-01-01	John	10	111		             10	John
2018-02-01	Peter	80	222		             20	Mike
2017-03-01	Peter	20	111		             30	Sam
2017-04-01	John	20	111				
2017-05-01	John	40	111				
2017-06-01	Peter	80	222				
2017-07-01	John	90	222				
2017-08-01	John	30	222				
2017-09-01	Peter	50	111				
2018-01-01	Peter	20	111				
2018-02-02	Sam	    35	222				

 

I want to calculate Amount only for managers that appear in ManagerTable. To do this I use TREATAS:

 

TestTreatas4 = CALCULATE(SUM(Sales[Amount]),TREATAS(VALUES(ManagerTable[Mgr1]),Sales[Manager])) 

 

However the result that I get is somewhat strange. Can not understand what makes the highlighted row to appear? Otherwise the measure seems to work just fine. If I change Peter row Date (in the source table) to something other than 1/1/2018, the row does not show up in the visual as expected.

 

Capture.PNG

 

What am I missing here?

 

2 ACCEPTED SOLUTIONS
PaulDBrown
Community Champion
Community Champion

The problem is that you are using the wrong "manager" field in the visual: you are using the field from the Sales table when you should be using the field from the manager table:

result.JPG

 

Treatas sum = CALCULATE([Sum Amount], TREATAS(VALUES(ManagerTable[Mgr1]), Sales[Manager]))

 

The TREATAS expression creates the virtual relationship "from" the "VALUES" expression to the corresponding field. In other words, the filter is from VALUES(ManagerTable[Mgr1]) to Sales[Manager] (The VALUES si the clue, since it creates a table with unique values, so the "one" side of the one-to-many relationship).

So you need to use the field from that VALUES expression in the visual.

Your alternative:

 

TestTreatas5 = 
CALCULATE(SUM(Sales[Amount]),VALUES(Sales[Manager]),TREATAS(VALUES(ManagerTable[Manager]),Sales[Manager])) 

 

works because you by introducing VALUES(Sales[Manager]), you are filtering to get unique manager values from the sales table, and then applying the TREATAS. So in practice the relationship then becomes VALUES(ManagerTable[Manager]), VALUES(Sales[Manger]) (so a one-to-one relationship).

Make sense?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

PaulDBrown
Community Champion
Community Champion

Ideally, in your scenario, you should create a dimension table with unique values for manager and join this dim table in a one-to-many relationship with the corresponding fields in your fact tables. You then use the dimension table field in measures, slicers, visuals, filters etc...

 

IF not, you need TREATAS (or a suitable alternative) in all your measures...





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

7 REPLIES 7
v-jingzhang
Community Support
Community Support

Hi @gvg 

 

There are two records on 2018/1/1 with the same product id. The amount in the highlighted row is from John's amount rather than Peter's amount. In this measure, TREATAS returns all the values exist in both Managers table's Mgr column and Sales table's Manager column, so the result is John and Sam. It calculates the total amount of John and Sam's sales. 

21091004.jpg

 

In image below, I use CONCATENATEX to combine the results returned by TREATAS(VALUES(Managers[Mgr]),Sales[Manager]) part. You will find Peter is excluded as expected. 

TestTreatas5 = CONCATENATEX(TREATAS(VALUES(Managers[Mgr]),Sales[Manager]),[Manager],", ")

21091005.jpg

 

In your second measure 

TestTreatas5 = 
CALCULATE(SUM(Sales[Amount]),VALUES(Sales[Manager]),TREATAS(VALUES(ManagerTable[Manager]),Sales[Manager])) 

VALUES(Sales[Manager]) part works as an additional filter in CALCULATE expression. On rows having "Peter", VALUES(Sales[Manager]) returns "Peter" while "Peter" doesn't exist in the result of TREATAS part, so the filtered result is blank.

 

Reference:

TREATAS – DAX Guide

A Deep Dive Into The TREATAS DAX Function In Power BI | Enterprise DNA

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Hi @v-jingzhang ,

Thank you for your explanation. Could you please elaborate on this part "On rows having "Peter", VALUES(Sales[Manager]) returns "Peter" while "Peter" doesn't exist in the result of TREATAS part.". If VALUES part is omitted, don't "Peter" row in the visual filter Sales leaving only the single row "1/1/2018 Peter" visible for CALCULATE?

gvg
Post Prodigy
Post Prodigy

Well, looks like the following measure works as expected:

TestTreatas5 = 
CALCULATE(SUM(Sales[Amount]),VALUES(Sales[Manager]),TREATAS(VALUES(ManagerTable[Manager]),Sales[Manager])) 

It is still mystery for me, why previous measure doesn't work. Looks like TREATAS does ALL behind the scenes.

PaulDBrown
Community Champion
Community Champion

The problem is that you are using the wrong "manager" field in the visual: you are using the field from the Sales table when you should be using the field from the manager table:

result.JPG

 

Treatas sum = CALCULATE([Sum Amount], TREATAS(VALUES(ManagerTable[Mgr1]), Sales[Manager]))

 

The TREATAS expression creates the virtual relationship "from" the "VALUES" expression to the corresponding field. In other words, the filter is from VALUES(ManagerTable[Mgr1]) to Sales[Manager] (The VALUES si the clue, since it creates a table with unique values, so the "one" side of the one-to-many relationship).

So you need to use the field from that VALUES expression in the visual.

Your alternative:

 

TestTreatas5 = 
CALCULATE(SUM(Sales[Amount]),VALUES(Sales[Manager]),TREATAS(VALUES(ManagerTable[Manager]),Sales[Manager])) 

 

works because you by introducing VALUES(Sales[Manager]), you are filtering to get unique manager values from the sales table, and then applying the TREATAS. So in practice the relationship then becomes VALUES(ManagerTable[Manager]), VALUES(Sales[Manger]) (so a one-to-one relationship).

Make sense?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown,

Great! It really makes sense. Never considered that. Thank you!

However if I place Manager from the ManagerTable I loose all the rest records from the Sales table. How do I keep them in the visual, but calculate Sales amount for the records from the ManagerTable only?

PaulDBrown
Community Champion
Community Champion

Ideally, in your scenario, you should create a dimension table with unique values for manager and join this dim table in a one-to-many relationship with the corresponding fields in your fact tables. You then use the dimension table field in measures, slicers, visuals, filters etc...

 

IF not, you need TREATAS (or a suitable alternative) in all your measures...





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






gvg
Post Prodigy
Post Prodigy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.