Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

TREATAS with variables as inputs

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.

 

 

7 Replies

  • DallasBaba's avatar
    DallasBaba
    Icon for Skilled Sharer rankSkilled Sharer
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.

  • DallasBaba's avatar
    DallasBaba
    Icon for Skilled Sharer rankSkilled Sharer
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

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

  • DallasBaba's avatar
    DallasBaba
    Icon for Skilled Sharer rankSkilled Sharer
    2 years ago

    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

      • DallasBaba's avatar
        DallasBaba
        Icon for Skilled Sharer rankSkilled Sharer
        2 years ago

        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.