Matrix measure counts through

DAX
1

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,

2

Hi @blondIT ,

It looks like you’re trying to solve a complex DAX calculation involving multiple tables with potentially large data sets. Here’s a step-by-step approach to create the required DAX formula:

  1. Count Players in DG_trx:
  2. Count Unique Transactions in IG_trx where DG_trx[trx_date] <= IG_trx[trx_date]:
  3. Matrix Calculation:

These steps outline the basic logic. Adjustments might be necessary based on the specifics of your data model and the performance considerations. Testing in a smaller subset of your data could also help fine-tune the performance. If performance is a significant issue, consider using calculated columns instead of measures for intermediate steps.

DAXCopy codeMatrix_Calculation = SUMMARIZECOLUMNS(    DG_trx[player_key],    DG_trx[product_key],    "DG Count", [DG_Players_Count],    "IG Count", [IG_Unique_Transactions])

DAXCopy codeIG_Unique_Transactions = CALCULATE(    DISTINCTCOUNT(IG_trx[trx_id]),    FILTER(        CROSSJOIN(DG_trx, IG_trx),        DG_trx[trx_date] <= IG_trx[trx_date] &&         DG_trx[player_key] = IG_trx[player_key] &&        DG_trx[product_key] = IG_trx[product_key]    ))

DAXCopy codeDG_Players_Count = COUNTROWS(DG_trx)

For more in-depth assistance and performance optimization, try running queries at the Data Mentor site.

Cheers,

Enterprise DNA Support Team

3

I’m sorry maybe there was a misunderstanding - I mean the condition “DG_trx[product_key] = IG_trx[product_key]” is wrong since for each table the product is different. What I need is for each player from the IG table to find all combinations of products from both tables (IG and DG ) for which the DG date_trx is after IG date_trx, and after that count them by Player from IG table, but anyway, BIG THANK YOU for trying, have a nice day,
blondIT

4

Hello @blondIT - I have tried the below Solution and it is matching with your desired results on Page 1 but do check for your bigger Dataset. Also, this is specific to your Matrix and may not work everywhere.

PlayerCount = var Base = SUMMARIZE(DG_trx,DG_trx[player_key],DG_trx[trx_date])

var Final = ADDCOLUMNS(Base,“CountR”,if(countrows(filter(IG_trx,IG_trx[player_key] = DG_trx[player_key] && IG_trx[trx_date] >= DG_trx[trx_date])) > 0,1,BLANK()))

return

sumx(Final,[CountR])

EDNA_MatrixMeasure.pbix (739.6 KB)

Thanks
Ankit J

1 Like
5

Hi Ankit J,

you are AMAZING :star_struck: , I can’t THANK YOU enough, this is what I was looking for, it is just perfect, I owe you a beer :beers: or chocolate :chocolate_bar: , actually a lot of beers / chocolates- whatever you prefer :innocent:, HAVE a GREAT DAY :partying_face:,
blondIT

6

Hi @blondIT - No problems. That’s why we are here to help each other. Special thanks to @EnterpriseDNA for the amazing courses and forum that helped people like me to learn amazing concepts of DAX and help others.

Thanks
Ankit J

1 Like