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

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]
            )