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

 

 

DateMajor CategoryCategorySubCategory
1-Jan-18AAAa1
1-Jan-18AAAa2
1-Jan-18AABa3
1-Jan-18AABa4
1-Jan-18BBAb1
1-Jan-18BBAb2
1-Jan-18BBBb3
1-Jan-18BBBb4
1-Jan-18BBBb5
2-Jan-18CCCc1
2-Jan-18CCCc2

 

Table 2

DateCategoryTarget
1-Jan-18AA8
1-Jan-18AB4
1-Jan-18BA2
1-Jan-18BB4
2-Jan-18CC7

 

And Get The following Result:

 

Basically, what I want is only the first row of each category in the same date is filled, the rest will become 0. Using INNER JOIN or LEFT JOIN wont work bc it will filll the first and second row for instance, with 8.

 

 

DateMajor CategoryCategorySubCategoryTarget
1-Jan-18AAAa18
1-Jan-18AAAa20
1-Jan-18AABa34
1-Jan-18AABa40
1-Jan-18BBAb12
1-Jan-18BBAb20
1-Jan-18BBBb34
1-Jan-18BBBb40
1-Jan-18BBBb50
2-Jan-18CCCc17
2-Jan-18CCCc20

 

any suggestion how to do it in the dax ?

 

Best regards

Andre

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

6 Replies

    • 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.

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