topic Re: TREATAS with variables as inputs in DAX Commands and Tips

TREATAS with variables as inputs

Anonymous — Tue, 17 Oct 2023 23:57:49 GMT

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.

Re: TREATAS with variables as inputs

DallasBaba — Wed, 18 Oct 2023 01:39:10 GMT

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!!!

Re: TREATAS with variables as inputs

Anonymous — Wed, 18 Oct 2023 01:12:43 GMT

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.

Re: TREATAS with variables as inputs

DallasBaba — Wed, 18 Oct 2023 01:38:19 GMT

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

Re: TREATAS with variables as inputs

Anonymous — Wed, 18 Oct 2023 02:57:00 GMT

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

Re: TREATAS with variables as inputs

DallasBaba — Wed, 18 Oct 2023 16:58:28 GMT

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

Re: TREATAS with variables as inputs

Anonymous — Wed, 18 Oct 2023 22:51:57 GMT

DallasBaba Still the same issue.

Re: TREATAS with variables as inputs

DallasBaba — Thu, 19 Oct 2023 00:38:18 GMT

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.