Combine two columns from different tables in SUMMARIZE() statement

[felipereyes_]

Hello everyone, 

I need to create a measure that has a virtual table that combines 2 columns from two different tables. Right now I only have the dax to combine two columns from the same table in the summarize function:

VAR table_0 =

    ADDCOLUMNS(

        CALCULATETABLE(

            SUMMARIZE(

                DIM_TERRITORIO_NO_RLS

                ,DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2]

                ,DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL1]

            ), REMOVEFILTERS(DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL1]), REMOVEFILTERS(DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2]),  DIM_TERRITORIO_NO_RLS[TERR_LINEA] = "G")

        , "CM GER"

            ,CALCULATE([CM% GER2 NO_RLS]

                ,REMOVEFILTERS(DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL1_FILTER], DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2_FILTER])

                ,REMOVEFILTERS(DIM_GEO_NO_RLS[DES_GEONIVEL3_FILTER], DIM_GEO_NO_RLS[DES_GEONIVEL4_FILTER]) )

        , "CM TU"

            ,CALCULATE([CM% TU NO_RLS]

                ,REMOVEFILTERS(DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL1_FILTER], DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2_FILTER])

                ,REMOVEFILTERS(DIM_GEO_NO_RLS[DES_GEONIVEL3_FILTER], DIM_GEO_NO_RLS[DES_GEONIVEL4_FILTER]) )

        )

I know it is a bit long and complicated but essentially, I am taking the columns DES_TERRNIVEL1 and DES_TERRNIVEL2 (both from the table DIM_TERRITORIO_NO_RLS), putting them into a virtual table and adding two measures with a handful of filters.

Now however, I need to combine DES_TERRNIVEL2 and the column DES_GEONIVEL2 which comes from the table DIM_GEO_NO_RLS, since I want to see the measure´s values of DES_GEONIVEL2 within DES_TERRNIVEL2. For context: the columns follow a hierarchy: DES_TERRNIVEL2 is the state, DES_TERRNIVEL1 is the county and DES_GEONIVEL2 is the city. So for this example I need to see the cities values within the states they belong to. 

I would really appreciate any kind of help!!

I tried this dax for now but it is not working:

VAR table_0 =

    ADDCOLUMNS(

        CALCULATETABLE(

            GENERATE(SUMMARIZE(

                DIM_TERRITORIO_NO_RLS

                ,DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2]), SELECTCOLUMNS(RELATEDTABLE(DIM_GEO_NO_RLS), "City", DIM_GEO_NO_RLS[DES_GEONIVEL2])

            ), REMOVEFILTERS(DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL1]), REMOVEFILTERS(DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2]),  DIM_TERRITORIO_NO_RLS[TERR_LINEA] = "B")

        , "CM Enoxa"

            ,CALCULATE([CM% Hibor TTO 3M Informe no_rls Felipe]

                ,REMOVEFILTERS(DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL1_FILTER], DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2_FILTER])

                ,REMOVEFILTERS(DIM_GEO_NO_RLS[DES_GEONIVEL3_FILTER], DIM_GEO_NO_RLS[DES_GEONIVEL4_FILTER]) ))

[ITManuel]

Hi @felipereyes_ ,

You can SUMMARIZE a table also based on columns of other table, if the the other table is on the "1" side of a 1-Many relationship.

Just 1 tip: If you provide code in your post, please format the code and insert it via the "Insert/Edit code sample" box. This will allow users to read and understand your code much more easily.

Br

[felipereyes_]

Hi Manuel, thanks for your reply. The table DIM_GEO_NO_RLS is however on the "many" side of the 1-many relationship since it has all the the cities that belong to the individual states that are in DIM_TERRITORIO_NO_RLS. I tried to do ti like this but it doesnt work:

SUMMARIZE(

                DIM_TERRITORIO_NO_RLS

                ,DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2]

                ,DIM_GEO_NO_RLS[DES_GEONIVEL2]

            )

do you have any suggestions maybe how it could work?

[ITManuel · Accepted solution]

Why not try 

SUMMARIZE (
    DIM_GEO_NO_RLS,
    DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2],
    DIM_GEO_NO_RLS[DES_GEONIVEL2]
)