Forum Discussion
Unexpected return from TREATAS function
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:
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?
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...
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.
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],", ")
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:
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?