Rank the max hourly value for multiple categories per date hierarchy

[Anonymous]

Hi all,

I need help with something that may be quite easy but I can't find the way to do it. 

I have a matrix that shows max values for meter hourly readings for several categories (rates types) 

I would like to have a rank for the hour and also for the max value of the day so I can use a what-if slicer to select the top N hours of the day and ideally to have another slicer to select the top N days for each month. This needs to be dynamic as users are able to select any combination of different rate types (up to 7) and data range.

I found a solution that I thought I would work https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/  which looks like this :

I tried the approach above but it doesn't seem to work with 'System CP'[date_key] as Category 😞

Rank Visual =

IF (

ISINSCOPE ('System CP'[RATE_CLASS_DESC] ),

RANKX (

CALCULATETABLE (

VALUES ('System CP'[RATE_CLASS_DESC] ),

ALLSELECTED ( ) --

),

[MAX_App_Power]

),

IF (

ISINSCOPE ( DIM_DATE[DATE]),

VAR DateAppPower = [MAX_App_Power]

RETURN

CALCULATE (

RANKX (

VALUES (DIM_DATE[DATE]),

[MAX_App_Power],

DateAppPower

),

ALLSELECTED ()

)

)

)

The model basically has a 'System CP' table which is joined to a Dim_Date Table with the Date_Key field

the 'System CP' table contains the following fields (this is the result of using the Group_by option in PQ): 

Date_key, Hour_of_day_Max_Apparent_power (per hour), Rate_class Code, Rate_Class_Description 

I also tried a measure like this: (https://www.youtube.com/watch?v=hX-s1htBdmk )

But as you can see, I am getting the top value for each hour of the day for each rate when what I really need is the top row for each day where the max values per rate don't really matter, I need to display only the N number of rows per day. ie. in the image below I would like to see only the row for the 19th hour of the day (7 pm) if a rank measure or top N in the above calculation would be =1 

Can someone help with a solution for this calculation?

TIA community!

[MFelix]

Hi @Anonymous ,

Be aware that measures are based on context so when you add the hour column to your matrix visualization you are adding context to your measure and then yoiu get the first value for each of the hours.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.