Drill-down from Date to Time Histogram

[PowerBI-Newbie]

Hi,

I've been searching for a way to drill-down from date to time but in the form of a histogram.

I have the following tables:

ISA Scores:

Date	Time	ISA Score
01/01/2024	09:15	1
01/01/2024	09:30	2
01/01/2024	10:00
02/02/2024	11:15	5

I am using a line graph to count the rows that have scores so on the 1st January 2024 the count will be 2, 2nd January will be 1, etc. And the graph will look like the following:

The y-axis will show the median scores and x-axis the date, and the tooltip will show the make up of that median number so on 1st January the median is 1.5 and on the 2nd January it's 5. Now when I drill down on the 1st January, I want to be able to see the scores for that day so between 09:00-10:00 there will be 2 markers, one for 09:15 and the other for 09:30 and all the other hours of the day it should be zero. And if I was to drill down to 2nd January then I should only see 1 marker (11:15) which is between 11:00-12:00.

Unfortunately, I'm getting the following:

A constant straight line across one of the values.

I appreciate any help that I can get.

[Ritaf1983]

Hi @PowerBI-Newbie 
To work with dates and times you need to create a date table + times table and use with the model.
Please refer to the linked tutorial :
https://radacad.com/how-to-use-time-and-date-dimensions-in-a-power-bi-model

And this great video of how to create a times table

https://www.youtube.com/watch?v=-q7v56p192M&t=2s

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

[PowerBI-Newbie]

Hi @Ritaf1983 , thank you very much for sending across those tutorials, they were very helpful and resolved a major issue that I was having. One of the things that I wanted to do, and perhaps it wasn't clear in my initial post, was to have a breakdown of the values within the same graph. The line graph is showing the median value for each day and when I drill down I want to be able to see how this median value is made up - e.g. if the median value for 20th February 2024 is 4 then when I drill down I should be able to see the values 3, 4 and 5 for example as a breakdown of the median 4 and these to be shown as separate markers on the same graph but I'm not getting that. What I'm getting is the median values at the time level when I drill down. It's probably because I'm using a measure calculating the median but is there a way for the graph to change how it displays the values such that at the highest level it'll show the median when drilled down it shows the values that make up this median value?

[Ritaf1983 · Accepted solution]

Hi @PowerBI-Newbie 
You can manipulate calculations by hierarchies with the function ISINSCOPE.
For example, if I have a graph of profits by year as a high level and date as a drill and I want to see the median of dates on the year level and daily profits on the drill the formula that i need to use is :

test =

if (ISINSCOPE('financials'[Date]),

sum([Profit]),

MEDIANX(SUMMARIZE('financials',financials[Date],"profits",sum('financials'[Profit])),[profits]))

Result on daily granularity :
and a median of the specific year on drill up:

more information about using inscope is here:

https://www.youtube.com/watch?v=xAZY9FyLDPo

pbix with my example is attached

 

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