Forum Discussion

andrehawari's avatar
andrehawari
Helper II
8 years ago
Solved

Join Two Table but with different granularity

Hi, I want to join these two tables Table 1     Date Major Category Category SubCategory 1-Jan-18 A AA a1 1-Jan-18 A AA a2 1-Jan-18 A AB a3 1-Jan-18 A AB a4 1-Jan...
Need Help
  • andrehawari's avatar
    andrehawari
    8 years ago

    Hi Ashish,

     

    Thanks for the solution. I can see that the general step you did: merge the query first, put the index, and update the value where the index is not 0.

     

    however, I am looking the solution in DAX formula, bc my powerBI will use live connection SSAS, so the power query options will not available there. 

     

     

     

    Thanks

    Andre

andrehawari's avatar
andrehawari
Helper II
8 years ago

Hi Ashish,

 

thanks for the help. I downloaded the excel, but where I can find the dax formula ?

Sorry for the basic questions

 

thanks

Andre

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

I have slved this using Data > Get & Transform, not DAX.  Click on any green coloured cell and go to Data > Queries and Connections > Queries and Connections.  On the right hand side pane, click on each query and see the steps in the Applied steps section.

  • andrehawari's avatar
    andrehawari
    Helper II
    8 years ago

    Hi Ashish,

     

    Thanks for the solution. I can see that the general step you did: merge the query first, put the index, and update the value where the index is not 0.

     

    however, I am looking the solution in DAX formula, bc my powerBI will use live connection SSAS, so the power query options will not available there. 

     

     

     

    Thanks

    Andre

    • bogomda's avatar
      bogomda
      Resolver I
      8 years ago

      EVALUATE
      VAR T1 =
          SELECTCOLUMNS (
              ADDCOLUMNS (
                  Table1,
                  "Key", Table1[Date] & Table1[Category],
                  "Subrank", VALUE ( MID ( Table1[SubCategory], 2, 15 ) )
              ),
              "Key", [Key],
              "Date", [Date],
              "Major Category", [Major Category],
              "Category", [Category],
              "SubCategory", [Subcategory],
              "Subrank", [Subrank]
          )
      VAR T2 =
          SELECTCOLUMNS (
              ADDCOLUMNS ( Table2, "Key", Table2[Date] & Table2[Category] ),
              "Key", [Key],
              "Target", [Target]
          )
      VAR T3 =
          NATURALLEFTOUTERJOIN ( T1, T2 )

      VAR T4 = 
          ADDCOLUMNS (
                  T3,
                  "Rank", VAR Ke = [Key] RETURN MINX(FILTER ( T3, [key] = Ke ),[Subrank] )=[Subrank])
      VAR T5 = ADDCOLUMNS(T4,"RevisedTarget", [Target] * [Rank])

      RETURN
          SELECTCOLUMNS(T5,
              "Date", [Date],
              "Major Category", [Major Category],
              "Category", [Category],
              "SubCategory", [Subcategory],
              "Target", [RevisedTarget]
              )