Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
Need a little help to format a matrix I'm building.
I have two tables:
I have created a key to link these two tables: Key = [IdLine]:[IdRoute]:[IdStop]
I have then created the following matrix in order to show differences between theoretical and actual stop sequence for a route. In order to do so I have displayed theoretical stop sequences on the left side and display actual passage time as value. To make it easier to read, I would like to put in red the cells for which the passage time is greater than that of the previous line (which means that there is a difference between theoretical and actual stop sequence).
Example down below :
I would really appreciate any help, thanks in advance !!
Solved! Go to Solution.
Thanks for the answer,
I have found a solution thnaks to the OFFSET function. I post it here in case someone is interested
1. Create a new table
Test = SUMMARIZE(timetable, timetable[LineId], timetable[Route], timetable[Trips], routes[StopSequence], timetable[StopId], timetable[ExpectedTimetable])
2. Create new measure and use it to apply conditional formating on timetable[ExpectedTimetable]
_offset =
VAR nextrow =
CALCULATE(
MAX(Test[ExpectedTimetable]),
OFFSET(
1,
ALLSELECTED(Test),
ORDERBY(Test[Trips], ASC, Test[StopSequence], ASC)
)
)
VAR currentrow =
CALCULATE(
MAX(Test[ExpectedTimetable]),
OFFSET(
0,
ALLSELECTED(Test),
ORDERBY(Test[Trips], ASC, Test[StopSequence], ASC)
)
)
RETURN
IF(nextrow <> BLANK() && nextrow < currentrow, "Red", "Black")
Thanks for the answer,
I have found a solution thnaks to the OFFSET function. I post it here in case someone is interested
1. Create a new table
Test = SUMMARIZE(timetable, timetable[LineId], timetable[Route], timetable[Trips], routes[StopSequence], timetable[StopId], timetable[ExpectedTimetable])
2. Create new measure and use it to apply conditional formating on timetable[ExpectedTimetable]
_offset =
VAR nextrow =
CALCULATE(
MAX(Test[ExpectedTimetable]),
OFFSET(
1,
ALLSELECTED(Test),
ORDERBY(Test[Trips], ASC, Test[StopSequence], ASC)
)
)
VAR currentrow =
CALCULATE(
MAX(Test[ExpectedTimetable]),
OFFSET(
0,
ALLSELECTED(Test),
ORDERBY(Test[Trips], ASC, Test[StopSequence], ASC)
)
)
RETURN
IF(nextrow <> BLANK() && nextrow < currentrow, "Red", "Black")
Hi @Gully3583,
Did you mean to find the records which bigger than new one? If that is the case, you can use current ‘stop Sequence’ and category group values to find out current next records and return color code to use as the condition formatting expressions.
formula =
VAR currSequence =
CALCULATE (
MAX ( Table[StopSequence] ),
ALLSELECTED ( Table ),
VALUES ( Table[LineID] ),
VALUES ( Table[Route] ),
VALUES ( Table[Trips] )
)
VAR currDate =
MAX ( Table[ExceptedTime] )
VAR nextDate =
CALCULATE (
MAX ( Table[ExceptedTime] ),
FILTER ( ALLSELECTED ( Table ), Table[StopSequence] = currSequence + 1 ),
VALUES ( Table[LineID] ),
VALUES ( Table[Route] ),
VALUES ( Table[Trips] )
)
RETURN
IF ( nextDate <> BLANK () && currDate > nextDate, "Red", "Black" )
Regards,
Xiaoxin Sheng
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |