Forum Discussion
SUMX with two tables
Hello,
Need some help with DAX.
I have two tables Historical Inventory data and Calendar table.
I need to calculate measure LS quantity. For the calculation I use data:
- Active days - sums up Active column
LS days - sums up LS column - Trusted - formula is if LS days is less than Active days*0.8 returns zero, so it can't be used in calculations
LS quantity =
VAR tbl= SUMMARIZE('Historical Inventory';'Historical Inventory'[KEY1]; "Active";[Active days];"LS"; [LS days]; "Sales"; SUM('Historical Inventory'[Sales]); "Trust"; [Trusted])
RETURN
SUMX(FILTER(tbl; [Trust]=1);
ROUND(
DIVIDE([Sales]; MAX([Active]-[LS days];1))*[LS days];1))
Everything works fine if I want statistical data for Item No., but when I want to see by the date it returns zero.
How should I modify DAX, to return correct LS quantity for both: Item No and date?
The issue to consider: this formula must be efficient, because it will be used in the big data table, so the latency must be as small as it can be.
Example file:
https://drive.google.com/file/d/14f_g9ht_hQFnYbVlemucBa1yfB_PnwS0/view?usp=sharing
2 Replies
- TeigeGao
Solution Sage
Hi Anonymous ,
We can use the following DAX query to get trusted:
Trusted = VAR Activedays = CALCULATE ( COUNTROWS ( 'Historical Inventory' ), 'Historical Inventory'[Active] = 1 ) VAR LSdays = CALCULATE ( COUNTROWS ( 'Historical Inventory' ), 'Historical Inventory'[LS] = 1 ) RETURN IF ( LSdays < Activedays * 0.8, 0, 1 )Best Regards,
Teige
- AnonymousNot applicable
Hi TeigeGao,
Thanks, for support, but maybe there was some misunderstanding, I don't have issues with calculating Trusted.
My problem is that I need to calculate LS quantity for items and for a date with one formula because for now, it calculates just for the item.