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:
In simple EXCEL language it would look like somethign like this:
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:
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?
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|
Here is an extract of the delivery table
|Delivery||Item||Material||Delivery quantity||Sales unit||Goods movement stat#||Reference document|
The table are linked between each other through intermediary matrix used as key connector since many-to-many are not allowed:
the desired table is a multi-column table that would display the following:
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:The 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:
Just 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.
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).
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:
In anticipation of your future questions:
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,