I need a DAX formula for matrix which will calculate the following:

1st fact table - DG_trx with transactions, 2nd fact table IG_trx, then there are Player dimension, and of course Date table. I need to calculate COUNT (not a DISTINCTCOUNT) of Players from DG_trx table who made transaction in a certain date - DG_trx [trx_date], and then I need to count all unique transactions for same Player from the table IG_trx where DG_trx [trx_date] <= IG_trx [trx_date] , split by DG_trx [product_key],

e.g. for DG_trx [player_key] = 1 the count must be like follows - see the picture, pls:

for D1 game → combinations D1 I1 and D1 I2

for D2 game → combinations D2 I1 twice and D2 I2 I need these combinations unique , so just D2 I1 and D2 I2 —> each combination is counted once

for D3 game → combinations D3 I1 and D3 I2

I’ve tried two different solution, but I’m a bit confused and not sure if this would work also in “real world” because the DG_trx and IG_trx tables will contain millions of rows and in my DAX formula there is CROSSJOIN used and I am concerned about performance.

The pbix file is attached.

MatrixMeasure.pbix (736.7 KB)

Can you help? BIG THANKS in advance,