Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JavierCL
New Member

Dax functions returns incorrect values

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:

JavierCL_0-1657715118909.png

 

JavierCL_1-1657715118911.png

 

 

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.

 

JavierCL_2-1657715118913.png

 

 

JavierCL_3-1657715118915.png

 

 

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.

JavierCL_4-1657715118923.png

 

 

The same is true for the MIN formula.

JavierCL_5-1657715118927.png

 

 

 

 

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.

 

JavierCL_6-1657715118931.png

 

JavierCL_7-1657715118932.png

 

 

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.

1 ACCEPTED SOLUTION
JavierCL
New Member

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.

View solution in original post

3 REPLIES 3
JavierCL
New Member

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
Super User
Super User

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

 

JavierCL_1-1657719865311.png

 

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... 😞

 

JavierCL_2-1657719982726.png

 

thank you for your response and help

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.