Forum Discussion
Solved
Using Treatas without Filtering the Dataset
Hi Everyone,
Hope you are doing good.
I have 2 sample datasets as below:
I have created a measure named
Amt_Total_Treatas =
CALCULATE(SUM(Amt[Amt]), TREATAS(VALUES('Group'[Cust]), Amt[Cust]))
I created this measure because i need to filter the Amt[Amt] from the Group table without creating relationship.
But when i checked the value of this measure, it only shows the amount for customers present in the Group table, (not those custs which absent in the Group table but present in the Amt table ):
Here are the data in each table if you need for example:
Amt
I created this measure because i need to filter the Amt[Amt] from the Group table without creating relationship.
But when i checked the value of this measure, it only shows the amount for customers present in the Group table, (not those custs which absent in the Group table but present in the Amt table ):
I want this measure to show the total value as 2968, not 1491 (perhaps showing the remaining amounts as blank). Can you help me with this?
(This is a sample dataset i created, in my original dataset i need to achieve the same thing without creating relationship)
Here are the data in each table if you need for example:
Amt
Group
I think the below should work. It will give the value for the current group if the group is in scope, otherwise it will give the total
Amt Total Treatas = VAR AllAmtNames = DISTINCT ( Amt[Cust] ) VAR AllGroupNames = DISTINCT ( ALL ( Group[Cust] ) ) VAR MissingAmtNames = EXCEPT ( AllAmtNames, AllGroupNames ) VAR Result = IF ( ISINSCOPE ( Group[Cust] ), CALCULATE ( SUM ( Amt[Amount] ), TREATAS ( VALUES ( Group[Cust] ), Amt[Cust] ) ), CALCULATE ( SUM ( Amt[Amount] ), TREATAS ( UNION ( VALUES ( Group[Cust] ), MissingAmtNames ), Amt[Cust] ) ) ) RETURN Result
4 Replies
I think the below should work. It will give the value for the current group if the group is in scope, otherwise it will give the total
Amt Total Treatas = VAR AllAmtNames = DISTINCT ( Amt[Cust] ) VAR AllGroupNames = DISTINCT ( ALL ( Group[Cust] ) ) VAR MissingAmtNames = EXCEPT ( AllAmtNames, AllGroupNames ) VAR Result = IF ( ISINSCOPE ( Group[Cust] ), CALCULATE ( SUM ( Amt[Amount] ), TREATAS ( VALUES ( Group[Cust] ), Amt[Cust] ) ), CALCULATE ( SUM ( Amt[Amount] ), TREATAS ( UNION ( VALUES ( Group[Cust] ), MissingAmtNames ), Amt[Cust] ) ) ) RETURN ResultYou can try
Amt_Total_Treatas = CALCULATE ( SUM ( Amt[Amt] ), TREATAS ( UNION ( VALUES ( 'Group'[Cust] ), { BLANK () } ), Amt[Cust] ) )Hi John,
Thanks for the comment, 🙂
However i am getting the same result for this as my previous measure, check below:
