Joining temp tables in DAX (TREATAS/CONTAINS/INTERSECT)

[smpa01]

Hello experts,

I have two temp tables and my dseired output is following

Table 4 =
VAR _master =

    DATATABLE (
        "ID", STRING,
        "BU", STRING,
        "YEAR", INTEGER,
        "Month", INTEGER,
        {
            { "BU1-2019-1", "BU-1", 2019, 1 },
            { "BU1-2019-2", "BU-1", 2019, 2 },
            { "BU1-2019-3", "BU-1", 2019, 3 }
        }
    )
VAR _actual =

    DATATABLE (

        "ID", STRING,
        "Amount", DOUBLE,
        {
            { "BU1-2019-1", 100.00 },
            { "BU2-2019-1", 200.00 },
            { "BU3-2019-1", 300.00 },
            { "BU1-2019-2", 1000.00 },
            { "BU2-2019-2", 2000.00 }
        }
    )
VAR _sum =
    GROUPBY ( _actual, [ID], "Amt", SUMX ( CURRENTGROUP (), [Amount] ) )

VAR _desiredwithleftjoin =

    NATURALLEFTOUTERJOIN ( _master, _sum )

RETURN

    _desiredwithleftjoin

I was wondering if there is a way to produce the same output by using TREATAS/CONTAINS/INTERSECT instead of NATURALLEFTOUTERJOIN.

Thank you in advance.

[parry2k]

@smpa01 in this case you are joining two tables together and I think you are using the best option here

[smpa01]

@parry2k  thanks for your feedback.

My question is

                        had those two tables not been temp and physical tables I could have exercised each of

                        TREATAS/CONTAINS/INTERSECT to generate the

                        NATURALLEFTOUTERJOIN equivalent output.

Is there a way to do the same with those tables when they are temp tables?

[parry2k]

@smpa01 well in that case it would haven been relationship between these two tables and everything just worked. At the end it was going to be relationship which was going to get you the result, and that's what you are doing in DAX

[smpa01]

@parry2k  TREATAS/CONTAINS/INTERSECT is particularly useful to create a virtual relationship (to create a relationship on the go) for the sake of calculation and it can be done with physical tables (two tables with no pre-established relationship).

I am trying to do a deepdive here to see if I can apply the same concept of virtual relationship for the sake of calculation when tables happen to be temp in nature.

That's all. 

[parry2k]

@smpa01 there has to be logical reason to use these functions, not just because the sake of using it. Read my blog post here where I used TREATAS as that was the only option.

[smpa01]

Sorry I disagrre with "there has to be logical reason to use these functions, not just because the sake of using it".

From my experience, it never hurts to learn more ways of doing the same thing as you never know when they might come handy.

Morover, I do have a purpose and that was the reason why I made a simplified post here. If you can answer what I am looking for (if DAX is capable of doing what I have in my mind) I can apply that to my situation.

[parry2k]

@smpa01 ofcourse you always want to learn more and these functions are there for reason, I didn't mean to say, you shouldn't learn but based on your example, none of these functions were relevent and that's why I pointed to blog post where I have to use TREATAS.

Not sure what else to say. Cheers!!

[smpa01]

Let me breakdown and rephrase my question.

Please find the workbook attached here

https://drive.google.com/file/d/1aWHbKrjAPFCv9rS69E8RHkhaxCkL4eTd/view?usp=sharing

I have shown how TREATAS, INTERSECT, CONTAINS can be used for cases when there is no pre-established relationship (in the attached workbook Table 5 and Table6). I have used them both for measures and calculated column (in Table 5).

Now, if I write a DAX quey and create identical temp tables I can achieve the same result by using

NATURALLEFTOUTERJOIN (in Table 7).

Is it possible to achieve the same result using any of TREATAS, INTERSECT, CONTAINS in Table 7?