Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
8 |