Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I have a measure that correctly identifies the largest value in the "Multi Search Visits" column.
The table/Measure name is '3 - Metric'[Multi Search Visits]
The measure has the value of 16638.
My goal is the find the date that matches this value, which s/b 10/19/2022, but it picks up the the latest date, not the correct one.
This is the measure I'm trying to use, which doesn't get me the specific date on the same row. I know the MAXX returns the latest date, but having trouble building it to find the specific date?
I tried, this didn't work
Solved! Go to Solution.
hi, @MRUry7
Hi @MRUry7 ,
Can you try the following:
Add a calculated column to your table like this:
IsMaxVisit =
VAR largestValue = CALCULATE([Measure that calculates largest value],ALL())
RETURN
IF('3 - Metric'[Multi Search Visits] =largestValue, "Yes","No")
When you add that column to the table visual it should say "Yes" exactly once, and "No" for all other occasions, except if you have multiple dates with the same exact visitor count.
Then you can make a measure like this:
Date with most visits =
CALCULATE(MIN('3 - Metric'[MetricDate]),'3 - Metric'[IsMaxVisit]="Yes")
And it should return you the correct date in a measure. Again, if you have multiple dates with the same visitor count, this measure will return the earliest (MIN) date. If you want the latest one replace with MAX. If you want your measure to show a list of all the dates where the view count equals the maximum, that will be a bit more complicated and I am not sure how to do that off the top of my head :/.
I hope this helps, let me know if you run into any issues.
Proud to be a Super User! | |
this worked absolutely terrific, and will be an excellent template to use with other measures that I have. Thank you!
Hi @MRUry7 ,
Can you try the following:
Add a calculated column to your table like this:
IsMaxVisit =
VAR largestValue = CALCULATE([Measure that calculates largest value],ALL())
RETURN
IF('3 - Metric'[Multi Search Visits] =largestValue, "Yes","No")
When you add that column to the table visual it should say "Yes" exactly once, and "No" for all other occasions, except if you have multiple dates with the same exact visitor count.
Then you can make a measure like this:
Date with most visits =
CALCULATE(MIN('3 - Metric'[MetricDate]),'3 - Metric'[IsMaxVisit]="Yes")
And it should return you the correct date in a measure. Again, if you have multiple dates with the same visitor count, this measure will return the earliest (MIN) date. If you want the latest one replace with MAX. If you want your measure to show a list of all the dates where the view count equals the maximum, that will be a bit more complicated and I am not sure how to do that off the top of my head :/.
I hope this helps, let me know if you run into any issues.
Proud to be a Super User! | |
hi, @MRUry7
this worked great and thank you! sure wish it was a bit simpler to do, such as in Excel.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
98 | |
80 | |
76 | |
66 |
User | Count |
---|---|
135 | |
109 | |
104 | |
83 | |
73 |