Hi all,
Having a struggle with a relatively simple DAX measure.
THE MEASURE
Sales :=
CALCULATE(
SUM('Transactions_Sales Line Items'[Sales ($) AUD]),
FILTER(
VALUES('Transactions_Sales Line Items'[SalesExclusion]),
'Transactions_Sales Line Items'[SalesExclusion] IN {"Keep", "Invoice Date Only"}
),
FILTER(
VALUES(Master_Products_US[In Sales Reporting]),
Master_Products_US[In Sales Reporting] IN {TRUE}
),
USERELATIONSHIP('Calendar'[Date], 'Transactions_Sales Line Items'[Invoice or CN Date])
)
FILTERS
The meause has been put into a table visual. Once slicing by Product Category (from the Master_Products_US table), the other by Sales Order (a column in the 'Transactions_Sales Line Items' table).
The only filters applied are over the calendar table to reduce the dataset being tested. No other filters are on the report.
THE ISSUE
For a select number of rows in the that have a SalesExclusion of "Invoice Date Only", the value is excluded from the report table when sliced by Product Category. The value is still shown in the overall total. I have thoroughly reviewed those rows and cannot determine how they differ in any way to the rows that pull through correctly.
When sliced by Sales Order, the measure returns blank against ALL individual SO rows. The total remains.
TESTING
I've completed many layers of testing including replacing the USERELATIONSHIP with an active relationship.
Removing the dependency on the product table and none of those items have had a meaningful impact.
except....
THE FIX
I was able to determine if I adjusted the filter over [SalesEclusion] to have ALL or ALLSELECTED, the result was correct. However, I don't understand why VALUES wasn't working correctly in this scenario and I cannot identify any cause. I'm therefore also concerned that it may have other implications I haven't yet found in testing.
Sales FIXED:=
CALCULATE(
SUM('Transactions_Sales Line Items'[Sales ($) AUD]),
FILTER(
ALL('Transactions_Sales Line Items'[SalesExclusion]),
'Transactions_Sales Line Items'[SalesExclusion] IN {"Keep", "Invoice Date Only"}
),
FILTER(
VALUES(Master_Products_US[In Sales Reporting]),
Master_Products_US[In Sales Reporting] IN {TRUE}
),
USERELATIONSHIP('Calendar'[Date], 'Transactions_Sales Line Items'[Invoice or CN Date])
)
THE ASK
Can anyone help with what could be a possible cause for this unusual behaviour? I'd like to understand why it is happening not only to ensure my solution is correct, but to ensure I don't create this error in the future.
I haven't been able to reproduce the error in a simple model and cannot share the original due to confidentiality.
