topic Re: How can you Normalise Measure Values (filtered by Column Value)? in DAX Commands and Tips

How can you Normalise Measure Values (filtered by Column Value)?

PowerBIUser1021 — Fri, 28 Aug 2020 07:00:41 GMT

Hello Power BI Community! ‌‌😊

Is there a way to normalise Measure values (filtered by a column value)?
For example, to do this I think I need min and max values of my measure. Below you can see the measure in a matrix table and find these values when they are filtered by a location (column value)...

5.3%
6.3%
11.2%
12.6%
10.3%
12.8%
9.8%
5.7%

But can I use DAX to show the min and max values of the above to show as per the below?

Max

5.3% 12.8%
6.3% 12.8%
11.2% 12.8%
12.6% 12.8%
10.3% 12.8%
12.8% 12.8%
9.8% 12.8%
5.7% 12.8%

Re: How can you Normalise Measure Values (filtered by Column Value)?

mhossain — Fri, 28 Aug 2020 07:58:23 GMT

PowerBIUser1021

Create a measure for MAX column

= Calculate(Max(Table[your value column]), removefilter(Table[your value column]))

Try above, if in your table you have multiple fileds then you probably would need to adjust the removefilter part in the dax.

Let me know if above is making sense

Re: How can you Normalise Measure Values (filtered by Column Value)?

amitchandak — Fri, 28 Aug 2020 09:22:05 GMT

PowerBIUser1021 ,

Try measures like

Max

calculate(max(Table[Value]) , all(Table))

calculate(max(Table[Value]) , allselected(Table))

Min

calculate(Min(Table[Value]) , all(Table))

calculate(Min(Table[Value]) , allselected(Table))

refer

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

Re: How can you Normalise Measure Values (filtered by Column Value)?

Anonymous — Fri, 28 Aug 2020 11:40:40 GMT

Please read about ALLSELECTED. This is the function to use but it's tricky.

https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

Re: How can you Normalise Measure Values (filtered by Column Value)?

PowerBIUser1021 — Sun, 30 Aug 2020 23:35:25 GMT

Thank you! I was able to use one of the formulas from this article.

MAXX(ALLSELECTED(column filter), [Measure]) and the same for min. Works perfectly!