Forum Discussion
Advocate VHow to create a time range filter in Power BI report? (Part 2)
OK, here should be a full solution for fact table:
Columns:
Hour = HOUR([Hours])
Minute = IF(MINUTE([Hours])<30,0,30)
Key = CONCATENATE(CONCATENATE([Hour],":"),[Minute])
Recreate that same key in your Hours table and relate them to one another.
dbadmin - OK, I finally had time to sit down with this and model it out. Here is what I did:
I have two Enter Data queries like this:
Shift
2:30:00 AM 3:00:00 AM 3:30:00 AM 4:00:00 AM 2:00:00 PM 2:30:00 PM 3:00:00 PM 3:30:00 PM Hour
2:22:03 PM 3:33:33 AM 2:23:23 PM 3:33:33 PM Shift table custom columns:
Hour = HOUR([Shift])
Minute = IF(MINUTE([Shift])<30,0,30)
Key = CONCATENATE(CONCATENATE([Hour],":"),[Minute])
Hours table custom columns:
Hour = HOUR([Hours])
Minute = IF(MINUTE([Hours])<30,0,30)
Key = CONCATENATE(CONCATENATE([Hour],":"),[Minute])
I could then create a measure like this in Shifts:
CountofHours = CALCULATE(COUNT(Hours[Hour]),RELATEDTABLE(Hours))
I could then create a visualization like:
Advocate VOK, I took a stab at it - but now it's not filtering at all. The relationship was created without any errors - not sure what's causing the hiccup now.
Data with KeyTime with Key
Community Championdbadmin - OK, I finally had time to sit down with this and model it out. Here is what I did:
I have two Enter Data queries like this:
Shift
| 2:30:00 AM |
| 3:00:00 AM |
| 3:30:00 AM |
| 4:00:00 AM |
| 2:00:00 PM |
| 2:30:00 PM |
| 3:00:00 PM |
| 3:30:00 PM |
Hour
| 2:22:03 PM |
| 3:33:33 AM |
| 2:23:23 PM |
| 3:33:33 PM |
Shift table custom columns:
Hour = HOUR([Shift])
Minute = IF(MINUTE([Shift])<30,0,30)
Key = CONCATENATE(CONCATENATE([Hour],":"),[Minute])
Hours table custom columns:
Hour = HOUR([Hours])
Minute = IF(MINUTE([Hours])<30,0,30)
Key = CONCATENATE(CONCATENATE([Hour],":"),[Minute])
I could then create a measure like this in Shifts:
CountofHours = CALCULATE(COUNT(Hours[Hour]),RELATEDTABLE(Hours))
I could then create a visualization like:
AlexGorevPower BI Team
Please also make sure you voted for the idea below...
Alex.
- AlexGorev
My bad, this was marked completed when Time Slicer custom visual was introduced. Link below points to the idea to have better native experience...
Thanks,
Alex.
- dbadmin
Greg_Deckler - Hey :) Thanks so much for trying to help me with this! I'm hopping back and forth right now between positions. It's taking me a little longer to respond than normal.
This isn't quite what I was going for - I don't think.
I'm trying to create a filter or a slicer that I can use to filter data. Kind of like with dates - but I can't get it to work properly.
I've got the Timetable created and I could create some other visuals I'm sure. But I need it as a filter on the report.
Is it possible to do this? I can't seem to find anything similar to it related to Power BI.
