cancel
Showing results for
Did you mean:
Member

## 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:

2 REPLIES 2
Community Support Team

## Re: SUMX with two tables

Hi @rimgri ,

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

Highlighted
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.

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 242 members 2,975 guests
Recent signins: