TREATAS with variables as inputs

[Anonymous]

My struggle to learn and comprehend the intricacies of TREATAS continues (not that I'm complaining, learning is fun).

Thanks for solving my previous problem, which was mainly self-inflicted by not paying attention, so here is a new one.

I have the following code calculating a table:

VAR A_TreatAs =
    TREATAS ( P_B, P_A[ProductKey], P_B[Name], P_B[Brand] )
VAR Result =
    NATURALLEFTOUTERJOIN ( P_A, A_Treatas )
RETURN

My previous issue has been solved and it works great.  The code uses two input table P_A and P_B which are also calculated tables created in a similar way, basically:

P_A = 
    UNION(
        SELECTCOLUMNS(table,"Name", column... etc

What I would like to do, is to do it without creating the intermediate tables so to have something like:

var a =
    UNION(
        SELECTCOLUMNS(P_A,"Name", column... etc

var b =
    UNION(
        SELECTCOLUMNS(P_B,"Name", column... etc

VAR A_TreatAs =
    TREATAS ( b, a[ProductKey], b[Name], b[Brand] )
VAR Result =
    NATURALLEFTOUTERJOIN ( a, A_Treatas )
RETURN
A_TreatAs

However I must be doing something wrong or missing something obvious again as TREATAS is not letting me use VARs as column names.

Any help would be appreciated.

[DallasBaba]

@Anonymous Thanks for letting me know error message using TREATAS Function. Let try using  the SUMMARIZE function

VAR A_TreatAs =
    TREATAS (
        SUMMARIZE(P_B, P_B[ProductKey], P_B[Name], P_B[Brand]),
        SUMMARIZE(P_A, P_A[ProductKey], P_A[Name], P_A[Brand])
    )

VAR Result =
    NATURALLEFTOUTERJOIN ( P_A, A_TreatAs )

RETURN Result

 The SUMMARIZE function will create temporary tables with the necessary columns, preserving their original names. This should resolve the issue with the TREATAS function. The TREATAS function expects fully qualified column references, and by using SUMMARIZE, you ensure that the column references are in the correct format.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Let me know if this works
Thanks

[Anonymous]

@DallasBaba Still the same issue.

[DallasBaba]

@Anonymous can you share the pbix sample data, or replace the code with the following and check if it works for your specific case.

VAR A_TreatAs =
    TREATAS (
        SUMMARIZE(P_B, P_B[ProductKey], P_B[Name], P_B[Brand]),
        ADDCOLUMNS(
            SUMMARIZE(P_A, P_A[ProductKey], P_A[Name], P_A[Brand]),
            "DummyColumn", 1
        )
    )

VAR Result =
    NATURALLEFTOUTERJOIN ( P_A, A_TreatAs )

RETURN Result

Note: A "DummyColumn" will be added to the SUMMARIZE result of P_A to make it a fully qualified table, which can then be used in the TREATAS function. This should bypass the "Function TREATAS expects a fully qualified column reference" error.

[DallasBaba]

@Anonymous you can use the ADDCOLUMNS function to create calculated tables, which preserve the original column names ("Name" and "Brand") from the 'P_A' table. This way, you can use 'P_A' and 'P_B' directly without having to create intermediate tables 'a' and 'b.'

The TREATAS function can then work with these tables without any issues.

VAR A_TreatAs =
TREATAS ( P_B, ADDCOLUMNS(P_A, "Name", P_A[Name], "Brand", P_A[Brand])[ProductKey], P_B[Name], P_B[Brand] )

VAR Result =
NATURALLEFTOUTERJOIN ( P_A, A_TreatAs )

RETURN Result

[Anonymous]

@DallasBaba That does not seem to work "Function TREATAS expects a fully qualified column reference as argument number 2."

[DallasBaba]

@Anonymous you can use calculated tables

PA =
VAR a = UNION(
SELECTCOLUMNS(P_A, "ProductKey", P_A[ProductKey], "Name", P_A[Name], "Brand", P_A[Brand])
)

VAR b = UNION(
SELECTCOLUMNS(P_B, "Name", P_B[Name], "Brand", P_B[Brand])
)

EVALUATE
VAR A_TreatAs =
TREATAS ( b, a[ProductKey], b[Name], b[Brand] )
VAR Result =
NATURALLEFTOUTERJOIN ( a, A_TreatAs )
RETURN
Result

The above measure will create calculated tables "a" and "b" with the UNION and SELECTCOLUMNS functions to structure the columns as needed.  

Let me know if this work
@ me in replies or I'll lose your thread!!!

[Anonymous]

@DallasBaba The calculated tables which are the inputs are used to unpivot the example P_A/P_B tables so I cannot use the column names from the P_A table.