Join Two Table but with different granularity

[andrehawari]

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-18	B	BA	b1
1-Jan-18	B	BA	b2
1-Jan-18	B	BB	b3
1-Jan-18	B	BB	b4
1-Jan-18	B	BB	b5
2-Jan-18	C	CC	c1
2-Jan-18	C	CC	c2

Table 2

Date	Category	Target
1-Jan-18	AA	8
1-Jan-18	AB	4
1-Jan-18	BA	2
1-Jan-18	BB	4
2-Jan-18	CC	7

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.

Date	Major Category	Category	SubCategory	Target
1-Jan-18	A	AA	a1	8
1-Jan-18	A	AA	a2	0
1-Jan-18	A	AB	a3	4
1-Jan-18	A	AB	a4	0
1-Jan-18	B	BA	b1	2
1-Jan-18	B	BA	b2	0
1-Jan-18	B	BB	b3	4
1-Jan-18	B	BB	b4	0
1-Jan-18	B	BB	b5	0
2-Jan-18	C	CC	c1	7
2-Jan-18	C	CC	c2	0

any suggestion how to do it in the dax ?

Best regards

Andre

[bogomda]

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

[Ashish_Mathur]

Hi,

You may download my solution from here.

Hope this helps.

[andrehawari]

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]

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 · Accepted solution]

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]

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