topic Re: I have 2 DAX expressions that do the same thing except in one product caculation in 1000's in DAX Commands and Tips

I have 2 DAX expressions that do the same thing except in one product caculation in 1000's

JimmySXS — Wed, 19 Feb 2025 13:56:06 GMT

Hi,
First off I am learning DAXand using data sources found on the web to practice. I have created a model and was practicing my DAX and wrote 2 expressions that I thought did the same thing.
The first one
1 Total Order Sales = SUMX(OrderLine, OrderLine[Sales] * OrderLine[Quantity])
then the next 2 to get the same value
2 Sales After Discount = SUMX(OrderLine, [Total Order Sales] *(1-OrderLine[Discount]))
3 Sales After Discount 2 = SUMX(OrderLine, (OrderLine[Sales] * OrderLine[Quantity]) * (1-OrderLine[Discount]))

It worked for 1000's of lines then not on 1 and I don't know why? Also I cannot find anywhere to attach the PBIX & xslx files I'm working from sorry.

Jim

Re: I have 2 DAX expressions that do the same thing except in one product caculation in 1000's

johnt75 — Wed, 19 Feb 2025 15:46:52 GMT

I think that there are duplicate rows in your data, probably in the order line table but possibly in one of the tables order line is related to.

In your first sales after discount measure you are iterating over order line and then performing context transition by calling the [Total Order Sales] measure. That will put all copies of the duplicate rows into the filter context and you will get the sum of all those duplicates multiplied by the discount.

In the second version you are not forcing context transition, you are only iterating over the table once, so each duplicate row will only be counted once, not once per the number of duplicates.

On the attachment point, you can put files on Google Drive, OneDrive or similar and share them from there.

Re: I have 2 DAX expressions that do the same thing except in one product caculation in 1000's

LYPowerBI — Wed, 19 Feb 2025 17:41:31 GMT

Re: I have 2 DAX expressions that do the same thing except in one product caculation in 1000's

Anonymous — Fri, 21 Feb 2025 06:37:57 GMT

Thanks for the reply from LYPowerBI and johnt75 , please allow me to provide another insight:

Hi, JimmySXS
Thanks for reaching out to the Microsoft fabric community forum.

Yes, as johnt75 mentioned and LYPowerBI verified, if the data itself contains duplicate rows, it can lead to inconsistent calculation results. The SUMX function inherently involves iteration. Below is a screenshot from the official documentation:

For more details, please refer to:

https://learn.microsoft.com/en-us/dax/sumx-function-dax#remarks

https://learn.microsoft.com/en-us/dax/dax-glossary#iterator-function

Here is my testing process:

1.This is my sample data:

The red box highlights the obvious duplicate data.

2..Here are the final results:

The results are inconsistent.

4.To modify this, you can try applying a DISTINCT() function to the Sales After Discount measure or the Total Order Sales measure before referencing the table. The final result is as follows:

Please find the attached pbix relevant to the case.

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: I have 2 DAX expressions that do the same thing except in one product caculation in 1000's

JimmySXS — Sat, 22 Feb 2025 11:37:27 GMT

Hi,

Thank you for your quick reply. I stillcannot find a place to attach my files and now I cannot cfind anywhere to change my password as I forgotton it.

JimmySXS

Re: I have 2 DAX expressions that do the same thing except in one product caculation in 1000's

JimmySXS — Sat, 22 Feb 2025 11:56:42 GMT

I tried to create a link like you suggested.

Download files

Re: I have 2 DAX expressions that do the same thing except in one product caculation in 1000's

Anonymous — Mon, 24 Feb 2025 02:10:56 GMT

Hi, JimmySXS

Thank you for your prompt response.

If you need to upload a PBIX file, I recommend using SharePoint or GitHub and attaching the file link in your reply. Please ensure that any sensitive information is removed.

Here are some links on how to upload data:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Best Regards,

Leroy Lu

Re: I have 2 DAX expressions that do the same thing except in one product caculation in 1000's

johnt75 — Tue, 25 Feb 2025 10:34:27 GMT

I created a [Diff] measure to show when the 2 measures differed, and you can see that there is a difference on e.g. 29 August 2012. Looking at the order lines for that day, using

DEFINE VAR _Date = TREATAS( { DATE( 2012,8,29) }, 'Calendar'[Date] ) EVALUATE CALCULATETABLE( OrderLine, _Date ) ORDER BY OrderLine[OrderID], OrderLine[ProductsID]

you can see that order ID 5907386 has a duplicate line for product 29. I'm sure there will be similar duplications for the other dates.

Re: I have 2 DAX expressions that do the same thing except in one product caculation in 1000's

JimmySXS — Thu, 27 Feb 2025 08:24:08 GMT

Hi Guys thanks for your replies but I don't think the data is duplicated. Here is one of the products that is incomplete