DAX bug or something fundamental I don't get?

[zaza]

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 

[Fowmy]

@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

[zaza]

@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

[Fowmy · Accepted solution]

@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