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 ChampionOK, 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.
hey greg , i am having some problem in power bi , i have a time column , i want to extract start hour, if minutes are less than thirty else end of our ,
for example 12:40:00-->13:00
12:30---->12:00
how can i do it in power BI