Showing results for 
Search instead for 
Did you mean: 
rimgri Member

SUMX with two tables



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])
    SUMX(FILTER(tbl; [Trust]=1); 
             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:



Community Support Team
Community Support Team

Re: SUMX with two tables

Hi @rimgri ,

We can use the following DAX query to get trusted:

Trusted =
VAR Activedays =
        COUNTROWS ( 'Historical Inventory' ),
        'Historical Inventory'[Active] = 1
VAR LSdays =
        COUNTROWS ( 'Historical Inventory' ),
        'Historical Inventory'[LS] = 1
    IF ( LSdays < Activedays * 0.8, 0, 1 )

Best Regards,


rimgri Member

Re: SUMX with two tables

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.

Helpful resources


How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 242 members 2,975 guests
Please welcome our newest community members: