topic DAX bug or something fundamental I don't get? in DAX Commands and Tips

DAX bug or something fundamental I don't get?

zaza — Sat, 19 Sep 2020 08:36:38 GMT

Lately I've been running into the weirdest errors. Here is one:

I have a simple table with 3 columns (Date, Open, Close) and create a measure to calculate the difference between the Open and Closed (the range):

Range = MAX ( 'Table'[Close] ) - MAX ( 'Table'[Open] )

Now I want to count the number of occurrence of each range.

First I create a calculated table with the top and bottom range:

Gen1 = GENERATESERIES ( -10, 10, 1 )

Then I count the rows for each value:

Count1 = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [Range] = MAX ( Gen1[Value] ) ) )

The results are as expected, nothing seems out of the ordinary (Gen table 1).

Now here is the most mind boggling behaviour I found:

I slightly modify my calculated table by incrementing by 0.5 instead of 1:

Gen2 = GENERATESERIES ( -10, 10, 0.5 )

This returns the exact same results as expected (Gen table 2).

Finally I change my table again, this time incrementing by 0.1:

Gen3 = GENERATESERIES ( -10, 10, 0.1 )

But this time the whole calculation breaks (Gen table 3).

I do not understand why this happens. Either I discovered a bug in DAX or there is something fundamental that I don't understand about DAX as by logic this should not be happening.

Edit: Here is the file for reference: PowerBI Mistery.pbix

Re: DAX bug or something fundamental I don't get?

Fowmy — Sat, 19 Sep 2020 11:20:24 GMT

zaza

I have encountered this scenario in a past question and it seems the rounding of decimals by DAX engine has a strange logic.

If you need to have all the numbers from the Gen 3 tables with values, you may try the measure as follows, adjust as per your need. then you get the results as.

Count3 = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [Range] = INT(MAX ( Gen3[Value] ) ) ) )

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Re: DAX bug or something fundamental I don't get?

zaza — Sat, 19 Sep 2020 16:25:30 GMT

Fowmy That's not right, I need the count of the numbers that actually appear in my dataset. I also cannot round up the numbers as in my real dataset I will have decimal place numbers.

If you say that there is a weird logic how DAX handles decimals, I would like to understand what is going on so I am able to do exact operations on decimal place values.

Why does the measure evaluete correctly against the first 2 and not the 3rd?

Gen1 = GENERATESERIES ( -10, 10, 1 ) //works Gen2 = GENERATESERIES ( -10, 10, 0.5 ) //works Gen3 = GENERATESERIES ( -10, 10, 0.1 ) //does not work

Re: DAX bug or something fundamental I don't get?

Fowmy — Sun, 20 Sep 2020 11:13:47 GMT

zaza

I have come up with a solution! I tested with decimal numbers as well and it works. Basically, I converted the [Range] measure and the Value from Gen3 Value to 1 decimal as a text result to compare. Test with decimal numbers to confirm.

The new measure:

Count3 = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', FIXED([Range],1) = FIXED(MAX ( Gen3[Value] ),1) ) )

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn