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...
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.
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?
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?
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...