Dax left join with dynamic filter on the second table

[vbourbeau]

Hi,

look the image below... and the code here https://we.tl/t-QhP7KEPuaT 

So we have 2 tables 'parts' and 'transaction' which are left joined to create the table 'leftjoin'

All work great with the dax command NATURALLEFTOUTERJOIN.

But my final goal is to filter the transaction table by date. So if I want only transaction since 2020-04-01 my final table must have only the C part with 2 all the other one must be empty.

Thank for your help.

[v-alq-msft]

Hi, @vbourbeau 

I'd like to suggest you modify the filter direction between 'Part' and 'transaction' as 'Both'. The pbix file is attached in the end.

Then you may create a measure as below.

Total Measure = 
var tab = ALLSELECTED('transaction')
return
SUMX(
    SUMMARIZE(
        Part,
        Part[Part],
        "Re",
        COUNTROWS(
            FILTER(
                tab,
                [Part2]=EARLIER(Part[Part])
            )
        )
    ),
    [Re]
)

Result:

Best Regards

Allan

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

[vbourbeau]

Thanks

But I need to see part without transaction. The goal it's to see the number of transaction for a period of time, even the part without transaction.

the result must be

A empty

B empty

C 2

D empty

...

[v-alq-msft · Accepted solution]

Hi, @vbourbeau 

You may modify the measure as below. The pbix file is attached in the end.

Total Measure = 
var tab = ALLSELECTED('transaction')
var result = 
SUMX(
    SUMMARIZE(
        Part,
        Part[Part],
        "Re",
        COUNTROWS(
            FILTER(
                tab,
                [Part2]=EARLIER(Part[Part])
            )
        )
    ),
    [Re]
)
return
COALESCE(result,0)

Result:

Best Regards

Allan

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

[gpiero]

@vbourbeau 

Hi, 
do you mean all visuals should display  only part C with total = 2 if date is >=01/04/2020 && <= TODAY()?

Regards

[vbourbeau]

@gpiero

No the final visual must display each part and the number. That alow us to see empty one.