Forum Discussion

RedDwarf_803's avatar
RedDwarf_803
Frequent Visitor
2 years ago
Solved

Display multiple values in same column/row in matrix

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
yy


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

 

  • giammariam's avatar
    giammariam
    2 years ago

    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.

4 Replies

      • giammariam's avatar
        giammariam
        Solution Sage
        2 years ago

        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.