cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kalo Frequent Visitor
Frequent Visitor

CALCULATE VALUE: applying Looked-up values filter

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.Pivot.PNG

     

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?

 

 

 

2 REPLIES 2
Super User
Super User

Re: CALCULATE VALUE: applying Looked-up values filter

Hi,

Share some data and show the expected result.

Highlighted
Kalo Frequent Visitor
Frequent Visitor

Re: CALCULATE VALUE: applying Looked-up values filter

Hi,

 

Here is an extract of the sales table

Purchase Order NumberDocument DateSales document typeSales documentSales Document ItemMaterialOrder Quantity (Item)Sales unit
00213905/29/19YPTA101064199010011550EA
00483205/09/19YPTA1008949010100115100EA
00509405/29/19YPTA101064611010011550EA
00507305/28/19YPTA101048491010011550EA

Here is an extract of the delivery table

DeliveryItemMaterialDelivery quantitySales unitGoods movement stat#Reference document
800445681010011550EAC10013090
8004458010100115100EAC10015485
800445861010011550EAC10020396
8004459010100115150EAC10021596
800445921010011550EAC10021605
800446081010011550EAC10028581
800492721010011550EAC10030933
8006336910100115200EAC10025798
800633791010011550EAC10034553
8006510310100115100EAC10038537
800749351010011550EAC10048016
800875521010011550EAC10056984
801225151010011550EAC10085475
8012744010100115100EAC10089490
801323981010011550EAC10093426
8014321610100115100EAC10102371
801460641010011550EAC10104849
801526581010011550EAC10110293
801526731010011550EAC10110314
801480381010011550EAC10106461
8004456010100115150EAC10010752
8004458810100115100EAC10020510
8004460210100115100EAC10028295
8004460610100115150EAC10028324
8004922710100115200EAC10015542
8006336810100115150EAC10025775
800633801010011550EAC10034558
801416261010011550EAC10101042
8001295010100115650EAC10007648
800445711010011550EAC10013104
8004458210100115100EAC10019082
8004923710100115200EAC10030919
8005396610100115300EAC10019082
801304391010011550EAC10091820
80063391101001150EAC10019082
80147359101001151000EAC10086935
800636611010011550EAC10019176
801526692010011550EAC10106419
8014590540100115100EAC10104751
801479849010011550EAC 
8006557411010011550EAC10040960
8006339190000110011550EAC10019082
80063391900002100115250EAC10019082
801445302010011550EAC10098012
8015445470100115100EAA10111813
8007259920100115100EAC10046251
8014300860100115100EAC10102158
8014609980100115100EAC10104857
8015266170100115100EAC10110296
800112298010011550EAC10005539
800492356010011550EAC10030907
800694027010011550EAC10043695
800784097010011550EAC10050355
800844036010011550EAC10054384
801040777010011550EAC10070119
801116097010011550EAC10076404
8012260210010011550EAC10085545
801274356010011550EAC10089484
801337729010011550EAC10094553
801367206010011550EAC10096931
801103823010011550EAC10060894
800786552010011550EAC10019245
8006982930100115250EAC10040979
8007106960100115300EAC10044938
8001616820100115100EAC10010470
8008607570100115100EAC10055738
8010103370100115100EAC10067583
800713618010011550EAC10045186
800783846010011550EAC10050338
800827947010011550EAC10052875
800892616010011550EAC10058271
800947849010011550EAC10062533
801041518010011550EAC10070202
801116139010011550EAC10076412
8012920310010011550EAC10090868
8006559411010011550EAC10040979
8011026610010011550EAC10075315
8014630920100115100EAC10105060
800088573010011510BXC10002565
8005395210100115100EAC10013009
8008959120100115350EAC10052847
80133789101001151500EAC10094579
8014135010100115600EAC10100786

 

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:Pivot.PNGTable 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:

1st fix pivot.PNGTable 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.

result.PNGTable 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).ranking 1st fix.PNGTable 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