Skip to main content
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

Frequent Visitor

New calculated column with the max of another calculated column



I'd appreciate your help a lot. I'm stuck with a problem I thought I could solve using measures instead of columns (I solved what I thought were similar problems doing so), but nothing worked so far, and I really need it. I have a table of countries with many columns; one of them is a calculated column called epi_risk made with other tables' measures and being updated from source every day. Here a capture of just the countries and that column:


Captura de pantalla 2021-03-02 a las 20.38.40.pngI need to standardize the Epi_risk distribution to a 0-10 scale. For that, I have tried this:


epi_risk_norm =
VAR max_er = MAX('country-region-subregion'[epi_risk])
VAR min_er = MIN('country-region-subregion'[epi_risk])
VAR e_r_n = ([epi_risk_m] - min_er) / (max_er -min_er)


But it works neither as a new calculated column nor new measure because the calculation iterates through all the rows, so (max_er - min_er) always is 0. How can I do it? Sure it will be a way to do it for but just starting with DAX, and I didn't find in the documentation a simple way.





I really appreciate any help you can provide. 






Super User
Super User


Your question is not clear.  Share some data or link to download your PBI file and show the expected result clearly.

Ashish Mathur

Hi @Ashish_Mathur , thank you very much for your interest. Sorry if I didn't make myself clear. I'll try to explain better as, unfortunately, I cannot share the PBI file because my organization doesn't authorize me to do it because it's internal for now. 


I have a table like the above with a column epi_risk (and with more columns and overall many other measures and calculated columns):


country    epi_risk     epi_risk_n
A                16            0,882012724
B                23,45       0,98973395
C                -32,78      0,176691729
D                24,16        1
E                  -9,8         0,508964719
F                   -45           0
G                  7,7           0,762001157

I want to get the column epi_risk_n as this calculation: 


country(i) - min(epi_risk) / max(epi_risk) -min(epi_risk) , so for the first row, country A, the value comes from (16-(-45)) / (24,16 - (-45)) = 0,8820. This is because I need to standardize the epi_risk in a scale from 0 to 10.


But I have failed till now. I could create neither a new calculated column nor a new measure. As I said in the previous post, If I try to create a new column, I get a circular dependency error:


Captura de pantalla 2021-03-02 a las 23.07.05.png

I tried then a new measure (I don't know why or what is behind but for previous similar calculations when I got the same problem and I switched to a measure, it worked), But on this occasion, it didn't:


Captura de pantalla 2021-03-02 a las 23.07.38.png

I don't get any error, but the calculation is done for each row, so max and min are the same and equal to the epi_risk value (so not the max of the entire column and the min of the entire column), so max-min is 0, so I get NaN for the entire column due to the division by 0.


I think it's important to mention that in the formulas above, country-region-subregion[epi_risk] is a calculated column, and [epi_risk_m] is a measure, so I cannot use transform data because the columns don't appear there.


I don't know if it's clear now, sorry if not; I don't know what else I could say, and I'd appreciate a lot your help because I really do need the values on a scale of 0 to 10.





Hi @pjporrino,


What is the data type of column[epi_risk]?

Maybe it is not supported to get max or min value in the same row.


Best Regards,


Frequent Visitor

Hi @smpa01 , thanks a lot for your help. It doesn't work because I get a circular dependency issue:

Captura de pantalla 2021-03-02 a las 23.07.05.png

I don't know if it's because the column epi_risk is a calculated column within the same table and with values from the same table. I got similar problems before, and I solved creating a measure instead of a column. I tried this:

Captura de pantalla 2021-03-02 a las 23.07.38.png

as a measure and [epi_risk_m] is the same as 'country-region-subregion'[epi_risk] but as a measure in the same table (I cannot use the column to define the variable e_r in a formula of a measure). 

In this case, I don't get the error regarding circular dependency, but it doesn't work, again division by 0.


Any idea?


Thank you very much,




Super User
Super User

@pjporrino  can you try the following





Column = 
VAR _1 = [val]
VAR _2 =CALCULATE(MIN('Table'[val]),ALLEXCEPT('Table','Table'[Country]))
VAR _3 = CALCULATE(MAX('Table'[val]),ALLEXCEPT('Table','Table'[Country]))
VAR _4= (_1-_2)
VAR _5 = (_3-_2)
VAR _6 = DIVIDE(_4,_5)


Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources


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.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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