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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pjporrino
Frequent Visitor

New calculated column with the max of another calculated column

Hi, 

 

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)
RETURN
e_r_n

 

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. 

 

Pedro

 

 

 

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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):

Table 

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.

 

Thanks,

 

Pedro

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,

Link

pjporrino
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,

 

Pedro

 

smpa01
Super User
Super User

@pjporrino  can you try the following

 

Capture.PNG

 

 

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)
RETURN _6

 

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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.