Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi
I want do display all events in categories (Cat) in a timeline matrix.
I have 7 Cat (A - G) and Cat A can have multiple events during one day.
The model is based on one fact table (FTT) and a timetable (TT). Fact table pic A
The timetable times is in 20 minutes intervals and is related to start time in FTT table. Pic B
Unfortunately I'm only able to display first event on each Cat in the timeline matrix. Pic C
Green frame illustrate first event for Cat A
Green frame in table to the right illustrate the start time and the end time
Red frame illustrate missing event for Cat A.
Red frame in table to the right illustrate the start time and the end time.
Start time 07:40 to End time 09:40 is missing in the matrix.
So the problem is my DAX only showing first event and I want to display all events.
My DAX measure
Is there a way to fix this problem?
Or is there another visual i BI that solves the problem (only free visuals)?
Pic A
Dummy data
Pic B
Pic C
Solved! Go to Solution.
@RedDwarf_803, Thanks for the .pbix file. Give this a shot and let me know if it's what you're after.
Test time 2 =
VAR time =
SELECTEDVALUE ( TT[Time] )
VAR startTime =
CALCULATE (
MIN ( FTT[Start time] ),
REMOVEFILTERS ( TT ),
FTT[End time] >= time
)
VAR endTime =
CALCULATE (
MAX ( FTT[End time] ),
REMOVEFILTERS ( TT ),
FTT[Start time] <= time
)
RETURN
IF ( time >= startTime && time <= endTime, 1, 0 )
If this is enough to get you going please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.
Hey @RedDwarf_803, thanks for the screenshot and descriptions. Could you provide the example .pbix file (here's how)? Happy to help.
@RedDwarf_803, Thanks for the .pbix file. Give this a shot and let me know if it's what you're after.
Test time 2 =
VAR time =
SELECTEDVALUE ( TT[Time] )
VAR startTime =
CALCULATE (
MIN ( FTT[Start time] ),
REMOVEFILTERS ( TT ),
FTT[End time] >= time
)
VAR endTime =
CALCULATE (
MAX ( FTT[End time] ),
REMOVEFILTERS ( TT ),
FTT[Start time] <= time
)
RETURN
IF ( time >= startTime && time <= endTime, 1, 0 )
If this is enough to get you going please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.
Thanks Madison. Your DAX works fine
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |