Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
Im having trouble filtering between a certain range of dates, and then after, filtering a certain range of hours.
I need all the incidents from 3/1/22 and 9/30/22. (That I can do with the sclicer)
Next, I need incidents only from 6:00 PM - 7:00AM between 3/1/22 - 9/30/22.
Thank you!
Solved! Go to Solution.
@amandabus21 Meant to be used in the Filters pane actually. The intent was to slice using your TimeTable. However, you may need two time tables, one to set a min and one to set a max. And, if you need to go from one day to the next (PM -> AM) then you will probably need to tweak the measure selector accordingly.
@amandabus21 Construct a TimeTable for your Time slicer:
HourTable =
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1,24,1),
"Hour",TIME([Value],0,0)
),
"Hour",[Hour]
)
Then create a Complex Selector like:
Time Selector Measure =
VAR __Opened = MAX('Table'[Date Opened])
VAR __MaxTime = MAX('TimeTable'[Hour])
VAR __MinTime = MIN('TimeTable'[Hour])
VAR __Time = __Opened - TRUNC(__Opened)
RETURN
SWITCH(TRUE(),
__Time >= __MaxTime, 1
__Time <= __MinTime && __Time >= 0, 1,
0
)
@amandabus21 As a table. In Report view in the Desktop, choose the Modeling tab and then New table and paste in that formula.
Hi Greg, thanks for your help. @Greg_Deckler
I was able to create the table but now when I create the measure I am getting a syntax error.
@amandabus21 Whoops, missed a comma after the first SWITCH condition:
Time Selector Measure =
VAR __Opened = MAX('Table'[Date Opened])
VAR __MaxTime = MAX('TimeTable'[Hour])
VAR __MinTime = MIN('TimeTable'[Hour])
VAR __Time = __Opened - TRUNC(__Opened)
RETURN
SWITCH(TRUE(),
__Time >= __MaxTime, 1,
__Time <= __MinTime && __Time >= 0, 1,
0
)
@Greg_Deckler thank you.
Now how can I use that measure to filter the time between 6PM - 7AM?
Should I make a new column or is it supposed to be used a slicer?
@amandabus21 Meant to be used in the Filters pane actually. The intent was to slice using your TimeTable. However, you may need two time tables, one to set a min and one to set a max. And, if you need to go from one day to the next (PM -> AM) then you will probably need to tweak the measure selector accordingly.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
18 | |
17 | |
16 |
User | Count |
---|---|
29 | |
27 | |
19 | |
15 | |
14 |