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

bogomda's avatar
bogomda
Resolver I
8 years ago

Here is link to DAX Excel solution Solution

Here is link to DAX Power BI Desktop solution - PowerBI Desktop Solution

if using Power Bi Desktop, don't put EVALUATE Statement, if use Excel copy it like shown below

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