Using Treatas without Filtering the Dataset

[Amit92]

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 ):

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

[johnt75 · Accepted solution]

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

[Amit92]

Thanks @johnt75 
You saved me 🙂 

[johnt75]

You can try

Amt_Total_Treatas =
CALCULATE (
    SUM ( Amt[Amt] ),
    TREATAS ( UNION ( VALUES ( 'Group'[Cust] ), { BLANK () } ), Amt[Cust] )
)

[Amit92]

Hi John,

Thanks for the comment, 🙂

However i am getting the same result for this as my previous measure, check below: