topic Re: Display multiple values in same column/row in matrix in DAX Commands and Tips

Display multiple values in same column/row in matrix

RedDwarf_803 — Sat, 14 Oct 2023 09:54:16 GMT

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

Test time =

Var sD = CALCULATE(MIN(FTT[Start time]),REMOVEFILTERS(TT) )

Var eD = CALCULATE(Min(FTT[End time]),REMOVEFILTERS(TT) )

Var xx= min(TT[Time])>=sd && MIN(TT[Time]) <= eD

Var yy=SWITCH(TRUE(),xx,1,0)

Return

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

Re: Display multiple values in same column/row in matrix

giammariam — Sat, 14 Oct 2023 15:49:11 GMT

Hey RedDwarf_803, thanks for the screenshot and descriptions. Could you provide the example .pbix file (here's how)? Happy to help.

Re: Display multiple values in same column/row in matrix

RedDwarf_803 — Sat, 14 Oct 2023 16:51:11 GMT

Dwarf.pbix

Re: Display multiple values in same column/row in matrix

giammariam — Sat, 14 Oct 2023 19:58:34 GMT

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.

Re: Display multiple values in same column/row in matrix

RedDwarf_803 — Sun, 15 Oct 2023 09:35:19 GMT

Thanks Madison. Your DAX works fine