Unable to find the matching value in a table

[Anonymous]

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

_M1 =

    VAR _TargetValue = '3 - Metric'[_Multi Search Visits]  

    VAR _FilteredTable = FILTER('3 - Metric', '3 - Metric'[Multi Search Visits] = _TargetValue)

RETURN

    MAXX('3 - Metric', '3 - Metric'[MetricDate])

   
Then I tried 

VAR _Found = LOOKUPVALUE('3 - Metric'[MetricDate],'3 - Metric'[Multi Search Visits],'3 - Metric'[_Multi Search Visits])

Any help is appreciated.
Steve

[Dangar332 · Accepted solution]

hi, @Anonymous 

M1 =

    VAR _TargetValue = calculate (max(Metric'[Multi Search Visits]),

                                                     all('3 - Metric'[MetricDate])

                                                     )

RETURN

    calculate(max('3 - Metric'[MetricDate]),

                  keepfilters('3 - Metric'[Multi Search Visits]=_TargetValue )

                   )

this give you only that date which has maximum  value of   ( '3 - Metric'[Multi Search Visits] )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

this worked absolutely terrific, and will be an excellent template to use with other measures that I have. Thank you!

[dk_dk · Accepted solution]

Hi @Anonymous ,

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.

[Dangar332 · Accepted solution]

hi, @Anonymous 

M1 =

    VAR _TargetValue = calculate (max(Metric'[Multi Search Visits]),

                                                     all('3 - Metric'[MetricDate])

                                                     )

RETURN

    calculate(max('3 - Metric'[MetricDate]),

                  keepfilters('3 - Metric'[Multi Search Visits]=_TargetValue )

                   )

this give you only that date which has maximum  value of   ( '3 - Metric'[Multi Search Visits] )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

this worked great and thank you! sure wish it was a bit simpler to do, such as in Excel.