I need to have a "card" show the name of a country that has the highest value from the latest data.

[Anonymous]

Hello,

I have tried searching for a solution but it seems that I cannot find an exact or similar answer that is simple to do.

I have an excel sheet with locations and relevant data with numerical values. 

Each location and value also has a date of collection. Example:

Location / Value / Date

X,   55,   31st Jan 2018

Y,   60,   31st Jan 2018

Z,   44,   31st Jan 2018

X,   39,   28th Feb 2018

Y,   34,   28th Feb 2018

Z,   58,   28th Feb 2018

If I just use TOPN for Value I would get Y because 60 is the highest. I just want to display the location with the highest value for the latest date which would be Z, however I can't use TOPN twice, (so selecting the latest date, and then selecting the topn from what remains) and I can't figure out how to make RANKX work on a CARD. (Because RANKX filtering only seems to work on a Table format and not a Card.)

I was able to make it work using two slicers that controlled the input into the box but then I have two slicers hanging around...

What is the solution for this?

[ibarrau · Accepted solution]

Hi, if you just want one name in card. This should work:

VAR fecha = MAX(Table[Date])
VAR valor = CALCULATE(MAX(Table[value]); Table[Date] =fecha)
VAR result = CALCULATE( MAX (Table[Location]); Table[Date] =fecha; Table[value]=valor )
RETURN
result

That will show the Location with max value y last date on context.

Hope this helps,

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

[Anonymous]

Hello,

Thanks for your help.

I thought I would be able to also use the result as a filter input for a visual to only show data for that location.

So then I could create a visual with the historical values that location has had.

However it seems that I have been unable to do so. If this was excel I would use a match lookup or something but I am really a beginner in power bi.

[ibarrau]

You can't use a measure to filter a result in a visualization. I think the best way to go here is having the ranking or list sorted. So you can right click on the first value and go its details in other page. That way you can build a hidden drillthrough page to accomplish this.

Regards,

[Anonymous]

Thanks!! Works like a charm!!!

In case someone wants to use this solution you may have to change the semicolons ";" to a commas "," for it to work properly.