Creating a measure using two different column from different table

[abchandru]

Need to write  DAX to calculate Qty  , IF Table1.Flag=1 and Table2.Date>=Table1.Date then sum(Qty) ELSEIF Table1.Flag=0 and Table2.Date<Table1.Date then sum(Qty) ELSE sum(Qty)

Joins based on CODE

Table1:

Code	Name	Group	Org	SKU	method	Flag	Date
561	KUM	KUM	1080	MB5	SO Portal
363	Syn	Syn	1020	MB5	Manual	0
363	Syn	Syn	1020	MB5	EDI_RPA	1	9/13/2022
259	TH	TH	1010	MB5	EDI_RPA
126	PBR	PBR	1020	MB5	SO Portal
127	BR	BR	1020	MB5	EDI_RPA	0
127	BR	BR	1020	MB5	SO Portal	1	10/3/2022

Table2:

Code	Name	Group	Org	SKU	Date	Qty
561	KUM	KUM	1080	MB5	10/3/2022	50
363	Syn	Syn	1020	MB5	7/23/2022	25
363	Syn	Syn	1020	MB5	10/24/2022	75
259	TH	TH	1010	MB5	7/25/2022	60
126	PBR	PBR	1020	MB5	10/5/2022	200
127	BR	BR	1020	MB5	9/17/2022	65
127	BR	BR	1020	MB5	10/6/2022	50
127	BR	BR	1020	MB5	10/21/2022	50

Result:

Code	Name	Group	Org	SKU	method	Qty
561	KUM	KUM	1080	MB5	SO Portal	50
363	Syn	Syn	1020	MB5	Manual	25
363	Syn	Syn	1020	MB5	EDI_RPA	75
259	TH	TH	1010	MB5	EDI_RPA	60
126	PBR	PBR	1020	MB5	SO Portal	200
127	BR	BR	1020	MB5	EDI_RPA	65
127	BR	BR	1020	MB5	SO Portal	100

[amitchandak]

@abchandru , If possible merge these two tables in the power query, you can select multiple columns while doing the merge

 https://radacad.com/append-vs-merge-in-power-bi-and-power-query

then you can easily create that as column or use in the filter in measure

[abchandru]

Hi Amit,

Thanks for reply,I am new to PowerBI tried to merge getting more records and how to write dax to achive 

[v-xiaosun-msft · Accepted solution]

Hi @abchandru ,

According to your description, I made a sample and here is my solution.

Create a calculated column.

Column =
VAR _a =
    MAXX ( FILTER ( 'Table1', [Code] = EARLIER ( Table1[Code] ) ), [Date] )
VAR _b =
    SUMX (
        FILTER (
            'Table2',
            'Table2'[Code] = EARLIER ( Table1[Code] )
                && 'Table2'[Date] <= _a
        ),
        [Qty]
    )
VAR _c =
    SUMX (
        FILTER (
            'Table2',
            'Table2'[Code] = EARLIER ( Table1[Code] )
                && 'Table2'[Date] > _a
        ),
        [Qty]
    )
VAR _d =
    SUMX ( FILTER ( 'Table2', 'Table2'[Code] = EARLIER ( Table1[Code] ) ), [Qty] )
RETURN
    SWITCH ( Table1[Flag], 0, _b, 1, _c, BLANK (), _d )

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.