Dax functions returns incorrect values

[JavierCL]

Problem definition: The MEDIANX, MAX/X, MIN/X, AVERAGEX and PERCENTILEX formulas do not give correct and coherent results.

The calculations of the times extracted and represented in the tables are correct, these are obtained, filtered and summed from Azure Storage, as can be seen in the following images:

When extracting the maximum with the Power Bi formula the result it gives is not correct, moreover it is different from the filtered result of the table. If we apply the MAXX formula the result it gives does not make sense.

The results are shown below:

- 68.50 is the result of the MAX formula, 3.99 with MAXX.

- 69.767 is the result of filtering the table data without applying the formula.

The same is true for the MIN formula.

These two formulas can be approached in other ways to obtain a correct visualization, but it does not apply to the remaining formulas, such as MEDIANX, AVERAGEX and PERCENTILEX for example. In these cases the results given are meaningless in the same way, and when filters are applied to separate times, erroneous results are still obtained.

Previously we used Power Bi desktop, it transformed and obtained the data directly from the source, and we did not have those problems. Currently due to the limitations, we have changed the process and we use Dataflow in Power Bi Service, to make the process of obtaining and transforming data, as I have shown at the beginning the data coming to the new Power Bi desktop are correct, since the times are equal and correspond to the original data source. The problem we have is generated when working with the data with the Power Bi formulas.

[JavierCL · Accepted solution]

after trying different ways to get the correct result, I got the solution to work in a new temporary table, I tried to use it directly in the same function, using summarize as parameter but it didn't work, however creating a table with the same formula the data shown were correct.

[Greg_Deckler]

@JavierCL It's hard to know for sure what is going on. I am pretty confident that functions like MAX and MAXX work perfectly fine but you need to understand how DAX works. For example, you don't need the CALCULATE in your first formula. Would need more information and sample data posted as text to replicate the issue you are having and figure out where the issue is.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[JavierCL]

sorry, I got the formula wrong because I was trying to make a temporary table and filter it by another field, I leave updated the image with the basic formula failing too.

the previous data we have is from 3 - 4 months ago. as I say the calculate is a residual error that I have left unintentionally after trying a thousand different ways, I have been more than 2 weeks searching, testing and simplifying to the minimum as max or min value and it does not work... 😞

thank you for your response and help