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:
Community Championdbadmin - Can you post some sample data? Especially a sample of the fact table? Posting the other tables (not the date table) would be helpful as well so that we can easily recreate the model and figure out what is up.
Advocate VSure!Data trying to filter
Time Table
Technically... I don't need a shift table - they want to be able to select whatever time values they want to - but inevitably if I DON'T include that option, they'll want it. But my first goal is to get this time table to work before I work in the shift table. Besides that, I haven't quite worked out all the kinks yet (2nd shift hours cross between two days).
THANKS!!!
- Greg_Deckler
OK, let me see if I can take this and create some sample data on my end. It will take me a little bit because I will have to key in all the data.
- Greg_Deckler
dbadmin - Actually, I think I have the answer without having to key in a bunch of data. In your fact table, create a new column:
Hour = HOUR([Hours])
Then, do the same thing in your Hours table essentially.
Then, relate the two tables based upon your "Hour" columns. That should get things right in your model.
Edit: OK that will take care if you only wanted it in Hours now that I think about but the same basic concept should apply, just come up with the same process but do it by the half hour. There is a MINUTE function and you could use that to decide if it is greater than or less than 30 and create your key with that in mind.
- Greg_Deckler
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.