Hi,
Sorry for long post but I am trying to build up my dax knowledge by really focusing on row context and iterator functions. As I was trying to replicate the virtual tables that are created when using nested iterator functions I came across some interesting discrepancies which hopefully someone can help me understand.
Please note that these queries are meaningless and I know there are more efficient ways to write these queries. I am focused on why combining different tables (but not changing the rows, filter context, context transition,etc) different results (only off on the rounding).
I am using the Adventure Works 2020 model for my data and I start with the following query in DAX Guide:
EVALUATE
{
format(
SUMX (
customer,
SUMX (
RELATEDTABLE ( sales ),
SUMX (
RELATEDTABLE ( 'Product' ),
'Product'[list price] * Sales[Unit Price] * sales[Order Quantity]
)
)
),"$#,##0.00000")
}
Dax Guide Result: $209,586,475,652.57000 (emphasis added by me)
When I look at the Vertipaq try to see how these nested sumx tables are being formed, I see the below:
SET DC_KIND="AUTO";
SELECT
'Customer'[CustomerKey], 'Product'[ProductKey], 'Internet Sales'[Order Quantity], 'Internet Sales'[Unit Price]
FROM 'Internet Sales'
LEFT OUTER JOIN 'Customer' ON 'Internet Sales'[CustomerKey]='Customer'[CustomerKey]
LEFT OUTER JOIN 'Product' ON 'Internet Sales'[ProductKey]='Product'[ProductKey];
Based on my limited understanding, I used the NATURALLEFTOUTERJOIN() function to try visualize the table. I created the below query in DAX Guide and Excel (using the tabular services trick to connect to Power BI then use the pivot table to create a table and then insert the DAX into the table).
EVALUATE
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(customer,sales),'Product')
Checking the vertipaq enging I see it performs the similar left outerjoin as the original sumx query (see below).
LEFT OUTER JOIN 'Customer' ON 'Internet Sales'[CustomerKey]='Customer'[CustomerKey]
LEFT OUTER JOIN 'Product' ON 'Internet Sales'[ProductKey]='Product'[ProductKey];
However, when I try to validate the result of the original sumx function by either either manually multipling and sum up the columns or applying sumx function to that newly created table, I get a different answer:
Query 1:Dax Guide Result: $209,586,475,652.53600
Query 1: Excel Result: $209,586,475,652.53600
Original SUMX Result:$209,586,475,652.57000
Variance between original SUMX result and Query 1: ($0.03366)
I know the variance is small, but I'm totally confused why I have one?
When I try to further simplify the query and remove the customer table as its not really doing anything other than confusing things, unfortunately things get a bit more confusing. For example:
EVALUATE
{
format(
SUMX (
'Product',
SUMX (
RELATEDTABLE ( Sales),
'Product'[list price] * Sales[Unit Price] * sales[Order Quantity]
)
)
,"$#,##0.00000")
}
Result= $209,586,475,652.56900
Original SUMX Result:$209,586,475,652.57000
And if I switch the order of the tables (Product and Sales)
EVALUATE
{
format(
SUMX (
Sales,
SUMX (
RELATEDTABLE ( 'Product'),
'Product'[list price] * Sales[Unit Price] * sales[Order Quantity]
)
)
,"$#,##0.00000")
}
Result :$209,586,475,652.57000
Original SUMX Result:$209,586,475,652.57000
Similar to above, when I try to visualize the two tables using the naturalleftouterjoin I get a different result from both the 3 table query or the original sumx query
EVALUATE
NATURALLEFTOUTERJOIN (Sales,'Product' )
Dax Guide Result: $209,586,475,652.49300
Excel Result: $209,586,475,652.49300
Original SUMX Result:$209,586,475,652.57000
And if I switch the order of the tables
EVALUATE
NATURALLEFTOUTERJOIN(sales,'Product')
Excel: 209,586,475,652.43800
DAX Editor:$209,586,475,652.43800
Original SUMX Result:$209,586,475,652.57000
I do notice something odd in dax studio when I hover over certain values, I see some extra decimals that I guess could add up over many rows to produce the extra rounding impacts:
For example, a value that shows as 3.14646 changes to 3.146460000000003
While I recognize it is a small variance, I'm still interested why there is any variance at all? Does the sumx have a rounding limitation / logic? Or does the naturalleftouterjoin() somehow add extensions to numbers which then changes the overall number? Also why does the table order and number of the joins change the results when the rows (with the relevant data) remain the same?
Hoping someone knows the right answer 🙂
