Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
To give some context, my fact table (Sales) is at a line item granularity but the carriage (listed on each line) is at the order granulaity.
e.g.
Product | Qty | Price | Carriage |
Apple | 3 | 0.50 | 4.50 |
Banana | 5 | 0.30 | 4.50 |
Lemon | 1 | 0.40 | 4.50 |
Value of goods = 3.40
Carriage = 4.50
Total Order Value = 7.90
To calculate the carriage I am creating temp table of each carriage value grouped by order number of all of the invoiced orders.
When writing this measure I tried two ways of creating the temp table, with both temp tables producing the exact same result in DAX studio/DAX Query View, however one doesn't work - returning a blank result. I think this might be due to data lineage (or a lack of) but I would like to know why?
Blank results from the FILTER version of the temp table:
InvoiceCarriageValueGBP =
VAR InvoicedCarriageByUniqueOrder =
FILTER(
ALL( 'Sales'[Transaction Id], Sales[Order Status], Sales[Order Carriage (GBP)]),
Sales[Order Status] = "Invoiced" &&
Sales[Order Carriage (GBP)] > 0
)
VAR Result = SUMX( InvoicedCarriageByUniqueOrder, Sales[Order Carriage (GBP)] )
RETURN
Result
Working result from the CALCULATETABLE/SUMMARIZE version:
InvoiceCarriageValueGBP =
VAR InvoicedCarriageByUniqueOrder =
CALCULATETABLE(
SUMMARIZE(
'Sales',
'Sales'[Transaction Id],
'Sales'[Order Status],
'Sales'[Order Carriage (GBP)]
),
'Sales'[Order Status] = "Invoiced" &&
'Sales'[Order Carriage (GBP)] > 0
)
VAR Result = SUMX( InvoicedCarriageByUniqueOrder, Sales[Order Carriage (GBP)] )
RETURN
Result
Note: There may be better ways of doing this calculation - I am still quite new at this DAX. If so, please let me know that too.
Hopefully, someone can help me understand the difference in outputs.
Thanks for taking the time to read this.
Oli
Hi @music43
In general it is hard to spot cause without good, really good knowledge of model, relationships...
Still, when using ALL with many columns check link and part below
The normal behavior for DAX expressions containing the ALL() function is that any filters applied will be ignored. However, there are some scenarios where this is not the case because of auto-exist, a DAX technology that optimizes filtering in order to reduce the amount of processing required for certain DAX queries. An example where auto-exist and ALL() provide unexpected results is when filtering on two or more columns of the same table (like when using slicers), and there is a measure on that same table that uses ALL(). In this case, auto-exist will merge the multiple filters into one and will only filter on existing combinations of values. Because of this merge, the measure will be calculated on the existing combinations of values and the result will be based on filtered values instead of all values as expected.
https://learn.microsoft.com/en-us/dax/all-function-dax?WT.mc_id=DP-MVP-4025372
Proud to be a Super User!
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |