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:
I need to standardize the Epi_risk distribution to a 0-10 scale. For that, I have tried this:
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.
Your question is not clear. Share some data or link to download your PBI file and show the expected result clearly.
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:
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:
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.
What is the data type of column[epi_risk]?
Maybe it is not supported to get max or min value in the same row.
Hi @smpa01 , thanks a lot for your help. It doesn't work because I get a circular dependency issue:
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:
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.
Thank you very much,
@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) RETURN _6
New Animated Dashboard: Sales Calendar
Click here to learn more about the September 2022 updates!
Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.
Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!