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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
skytulip33
Frequent Visitor

How to create a measure to show the max value of another filtered measure

I have create a matrix with two columns, the column names are "year" and "visitor". "Visitor" is actually a measure filtered by a date type measure "Year". The table structure is like this:

YearVisitor
2017260
2016220
2015230
2014210

 

What I would like to do is to add another column into this matrix. The third column is named "MaxVisitor". I would like to have this column to always display the Maximum value of the "Visitor" measure, the way I would like it to display is like this:

YearVisitorMaxVisitor
2017240260
2016260260
2015230260
2014210260

as you can see that the third column "MaxVisitor" always displays 260 which is the largest value from the column (i.e the filtered measure) "Visitor".

 

I am not sure if this can be done?

 

Any advice will be appreciated!

1 ACCEPTED SOLUTION

That's correct. I totally forgot that it should have used the year instead of Customer Group. Well done on figuring it out




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

10 REPLIES 10
GilbertQ
Super User
Super User

Hi @skytulip33

 

This measure should work for you where you table is called "Table3"

 

Max Visitor = CALCULATE(MAX('Table3'[Visitor]),ALL('Table3'))




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

What if Visitor is a measure created and not a loaded column, you cant use MAX fucntion as it wil give you error: could not be found
I tried using MAXX but still have issues

 

please guide

Thanks

Abhilash.

Hi @GilbertQ,

 

Thanks for your reply. I tried your solution but it seemed that it doesn't work.

 

The value calculcated from your formula seems to be the SUM of all visitors rather than the Max visitors filtered by the Year? It seemed that the "year" filter is ignored by that formula?

 

Hi @skytulip33

 

Please see the image below where I have taken your data and put it with the measure into Power BI.

 

Email Pic.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ

 

Thank you for that. I tried again and realized that I missed sth. in my description. I didn't realize that it would cause this difference before. 

 

I need to modify my description, that is, what if the customers are divided into two goups? say the original data is like:

 

YearVisitorCustomer Group
20172601
20162201
20152301
20142101
20172652
20162622
20151582
20142222

 

and when I tried to use your solution, it seemed that the MaxVisitor will be incorrect for Group 1. As can be seen in the pic:

 

1.png

 

Do you mind giving some suggestion on this?

 

Thank you!

Hi @skytulip33 

 

What if you had to try this measure.

 

Max Visitor = CALCULATE(MAX('Table3'[Visitor]),ALLSELECTED('Table3'[Customer Group]))




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ

Thanks for your reply. I did a bit of test and modified a bit on your suggested solution, the following one works fine:

 

MaxVisitor = calculate(max(Table3[Visitor]),ALLSELECTED(Table3[Year]))   

That's correct. I totally forgot that it should have used the year instead of Customer Group. Well done on figuring it out




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ

Thank you for your help!

Glad to assist you




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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