Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Combine two columns from different tables in SUMMARIZE() statement

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]) ))
Need Help
Tips and Tricks
Tutorial Requests
  • ITManuel's avatar
    ITManuel
    2 years ago

    Why not try 

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

3 Replies

  • ITManuel's avatar
    ITManuel
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    Hi Anonymous ,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        ITManuel
        Icon for Responsive Resident rankResponsive Resident
        2 years ago

        Why not try 

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