CALCULATE VALUE: applying Looked-up values filter

[Anonymous]

Dears,

I've been trying to add a filter via CALCULATE() on an expression, based on the existance of a common reference in both the implicated tables.

To do so, My best idea was to develop my filter around the LOOKUPVALUES function that seemed perfectly fit for the job, but turns out it did not work. Then I tried to work with a FILTER(AND()) structure, a FILTER(COUNTROWS(VALUES())>0) strucuture as well as HASONEVALUE(), but still not a great result.

In few words, the condition I'm trying to express is the following:

• Sum the Qty if the sales ref. are listed on both tables

In simple EXCEL language it would look like somethign like this:

• If(and(vlookup([sale ref.],tabe1,1,FALSE),vlookup([sale ref.],tabe2,1,FALSE)),sumif([@table2[material]],[@table3[material]],[table2[qty]])

As a  DAX function it currently look like this: 

Shipped:=CALCULATE(SUM('Outbound'[Delivery quantity]),FILTER('Outbound',COUNTROWS(VALUES(SALES[Sales document]))>0))

Please note these two important element:

1. Both tables are linked between one another through several connection Key, namely the sale ref and the material.
2. This formula returns the total Qty delivered without applying the filter to the qty value, what leads to impossible result when the filter context is amended to not display the sales ref anymore in the table.

The information contained in outbound include lines that are not listed into sales, hence the need for a filter that only includes the sales ref that are listed in both tables to avoid this discrepancy. I've tried to do it using the connection key but it's not filtering anything.

is the current code a dead-end? I appear to be currently unable to translate the lookupvalue condition into an effective filter.The only alternative I can think of as of now is going through a CREATETABLE structure to filter out the unwanted sales ref.

What would be your take on my current problem?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Hi,

Here is an extract of the sales table

Purchase Order Number	Document Date	Sales document type	Sales document	Sales Document Item	Material	Order Quantity (Item)	Sales unit
002139	05/29/19	YPTA	10106419	90	100115	50	EA
004832	05/09/19	YPTA	10089490	10	100115	100	EA
005094	05/29/19	YPTA	10106461	10	100115	50	EA
005073	05/28/19	YPTA	10104849	10	100115	50	EA

Here is an extract of the delivery table

Delivery	Item	Material	Delivery quantity	Sales unit	Goods movement stat#	Reference document
80044568	10	100115	50	EA	C	10013090
80044580	10	100115	100	EA	C	10015485
80044586	10	100115	50	EA	C	10020396
80044590	10	100115	150	EA	C	10021596
80044592	10	100115	50	EA	C	10021605
80044608	10	100115	50	EA	C	10028581
80049272	10	100115	50	EA	C	10030933
80063369	10	100115	200	EA	C	10025798
80063379	10	100115	50	EA	C	10034553
80065103	10	100115	100	EA	C	10038537
80074935	10	100115	50	EA	C	10048016
80087552	10	100115	50	EA	C	10056984
80122515	10	100115	50	EA	C	10085475
80127440	10	100115	100	EA	C	10089490
80132398	10	100115	50	EA	C	10093426
80143216	10	100115	100	EA	C	10102371
80146064	10	100115	50	EA	C	10104849
80152658	10	100115	50	EA	C	10110293
80152673	10	100115	50	EA	C	10110314
80148038	10	100115	50	EA	C	10106461
80044560	10	100115	150	EA	C	10010752
80044588	10	100115	100	EA	C	10020510
80044602	10	100115	100	EA	C	10028295
80044606	10	100115	150	EA	C	10028324
80049227	10	100115	200	EA	C	10015542
80063368	10	100115	150	EA	C	10025775
80063380	10	100115	50	EA	C	10034558
80141626	10	100115	50	EA	C	10101042
80012950	10	100115	650	EA	C	10007648
80044571	10	100115	50	EA	C	10013104
80044582	10	100115	100	EA	C	10019082
80049237	10	100115	200	EA	C	10030919
80053966	10	100115	300	EA	C	10019082
80130439	10	100115	50	EA	C	10091820
80063391	10	100115	0	EA	C	10019082
80147359	10	100115	1000	EA	C	10086935
80063661	10	100115	50	EA	C	10019176
80152669	20	100115	50	EA	C	10106419
80145905	40	100115	100	EA	C	10104751
80147984	90	100115	50	EA	C
80065574	110	100115	50	EA	C	10040960
80063391	900001	100115	50	EA	C	10019082
80063391	900002	100115	250	EA	C	10019082
80144530	20	100115	50	EA	C	10098012
80154454	70	100115	100	EA	A	10111813
80072599	20	100115	100	EA	C	10046251
80143008	60	100115	100	EA	C	10102158
80146099	80	100115	100	EA	C	10104857
80152661	70	100115	100	EA	C	10110296
80011229	80	100115	50	EA	C	10005539
80049235	60	100115	50	EA	C	10030907
80069402	70	100115	50	EA	C	10043695
80078409	70	100115	50	EA	C	10050355
80084403	60	100115	50	EA	C	10054384
80104077	70	100115	50	EA	C	10070119
80111609	70	100115	50	EA	C	10076404
80122602	100	100115	50	EA	C	10085545
80127435	60	100115	50	EA	C	10089484
80133772	90	100115	50	EA	C	10094553
80136720	60	100115	50	EA	C	10096931
80110382	30	100115	50	EA	C	10060894
80078655	20	100115	50	EA	C	10019245
80069829	30	100115	250	EA	C	10040979
80071069	60	100115	300	EA	C	10044938
80016168	20	100115	100	EA	C	10010470
80086075	70	100115	100	EA	C	10055738
80101033	70	100115	100	EA	C	10067583
80071361	80	100115	50	EA	C	10045186
80078384	60	100115	50	EA	C	10050338
80082794	70	100115	50	EA	C	10052875
80089261	60	100115	50	EA	C	10058271
80094784	90	100115	50	EA	C	10062533
80104151	80	100115	50	EA	C	10070202
80111613	90	100115	50	EA	C	10076412
80129203	100	100115	50	EA	C	10090868
80065594	110	100115	50	EA	C	10040979
80110266	100	100115	50	EA	C	10075315
80146309	20	100115	100	EA	C	10105060
80008857	30	100115	10	BX	C	10002565
80053952	10	100115	100	EA	C	10013009
80089591	20	100115	350	EA	C	10052847
80133789	10	100115	1500	EA	C	10094579
80141350	10	100115	600	EA	C	10100786

The table are linked between each other through intermediary matrix used as key connector since many-to-many are not allowed:

• Material 
• Sales document & reference document
• Sales unit

the desired table is a multi-column table that would display the following:

• material 
• Order qty:=SUM(sales[Order Qty(item)]
• Shipped:= TO BE DETERMINED
• Balance:= [Order Qty]-[Shipped]

Please note that beside the Material, all other entries of the table are calculated field. Ultimately, the goal would be to use the Balance measure in the same table context but with other calculated values for further development.

As of now the detail result is as following:Table 1The Shipped measure used is currently as following:

shipped:=CALCULATE(
                   SUM('Delivery'[Delivery quantity]),
                       FILTER('Delivery',
                              COUNTROWS(
                                        VALUES(SALES[Sales document]))>0))

As you can see the formula works perfectly at a row context level when the Sales # are added to the table. But the formula does not function anymore when the Sales # are removed to meet the desired table context (table 2). I'm not supposed to get negative balance since the qty shipped cannot exceed the qty ordered. 

In addition, the totals in table 1 are not aligned with the displayed information. This is due to the fact that there is more value in the delivery table and that the filter I'm applying is not effective at all. I've also tried with HASONEVALUE() but the result is not better. the current filter is only effective in term of filtering the row display display in table 1 to show only the desired information, The current filter does not apply a filter on the calculated values themselves. Therefore, when the desired table context is prompted the following result occurs:

Table 2Just like the grand total of table 1, each line of the desired table displays the total of the qty shipped per Material regardless of the condition of the filter. Hence the need for a better filter. If we go through the same information as table 2 adding the sales # in the table context (table 3), we discover that actually a total of shipped Qty is about 252, thus a balance 22.

Table 3

To make it clear, the filter condition for the sum of qty shipped should be: 'consider only the value for which the sales # that are listed in both table'. Hence only 4 distinct Sales # should be considered for the calculation, prompting thus the correct total regardless of the table context (w/o sale #), and the total should be coherent (table 4).Table 4. Desired result

When I started to work on this with a Basic EXCEL point of view, LOOKUPVALUE() seemed perfect for the job but turns out we cannot "call" the sum of a column. the reason why is well understood and documented. But I still need to get the job done, despite trying the work arround with HASONEVALUE() or COUNTROW()>0.

If I approach now this problem with a SQL point of view, the logic want that I'd do an inner join query based on the common sales ref between the two tables to get the desired information out of the delivery table. Therefore, I think the DAX formula might run around a filter that create a table with an INNER JOIN kind of structure to use as such, but I still need to get it to work without error:

Shipped:=CALCULATE(
                  SUM('Delivery'[Delivery quantity]),
                     FILTER(
                           VALUES('Delivery'[Reference document]),
                                 NATURALINNERJOIN(
                                                 VALUES(SALES[Document reference]),
                                                       VALUES('Delivery'[Reference document]))))

In anticipation of your future questions:

• No, I don't want to create a new table, Dax should iterate through a virtual table.
• Yes, I'd like to hold to my measure as much as possible to keep my data model optimized 

I hope my additional information have been exhaustive enough.

Please let me know your take on this, I'm pretty sure I'm not close to get a solution quite yet.

Most importantly, do indicate me where I'm mistaken in my approach.

Thank you for your time,

Kalo