Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
I am trying to visually compare scheduled hours with completed hours to identify incomplete shifts. Despite my attempts, I have been unable to achieve the desired result. I have attached a sample workbook that includes both tables and the intended outcome for reference.
Here is a brief overview of what I have been attempting:
However, my current implementation has not yielded the expected results. Therefore, I am seeking your expertise to help me resolve this issue. I would greatly appreciate any guidance or insights you can provide.
Please find the attached sample workbook, which includes the necessary tables and the desired outcome, for your reference. Your assistance in this matter would be invaluable to me.
Thank you for your time and consideration
Dii
Scheduled table | |||
Category | Worker ID | start_date_time | end_date_time |
A | 1 | 2022-10-24 10:00:00 | 2022-10-25 18:00:00 |
A | 1 | 2022-10-26 10:00:00 | 2022-10-26 18:00:00 |
B | 2 | 2022-10-17 14:00:00 | 2022-10-17 22:00:00 |
B | 2 | 2022-10-05 17:00:00 | 2022-10-06 00:00:00 |
C | 3 | 2022-10-06 17:00:00 | 2022-10-06 00:00:00 |
Completed table | ||||
Worker ID | start_date_time | end_date_time | duration | Category |
1 | 2022-10-24 10:00:00 | 2022-10-25 18:00:00 | 8 | A |
2 | 2022-10-17 14:00:00 | 2022-10-17 22:00:00 | 0 | B |
2 | 2022-10-05 17:00:00 | 2022-10-06 00:00:00 | 7 | B |
Solved! Go to Solution.
Hi @Aknas ,
According to your description, here's my solution.
Column in the matrix is of text format in my sample.
Create two measures.
Value =
IF (
MAX ( 'Table'[Column1] )
IN SELECTCOLUMNS (
'Scheduled',
"date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
)
&& MAX ( 'Table'[Column1] )
IN SELECTCOLUMNS (
'Completed',
"DATE", FORMAT ( 'Completed'[start_date_time], "DD-MMM" )
),
" ",
IF (
MAX ( 'Table'[Column1] )
IN SELECTCOLUMNS (
'Scheduled',
"date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
),
" "
)
)
Color =
IF (
MAX ( 'Table'[Column1] )
IN SELECTCOLUMNS (
'Scheduled',
"date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
)
&& MAX ( 'Table'[Column1] )
IN SELECTCOLUMNS (
'Completed',
"DATE", FORMAT ( 'Completed'[start_date_time], "DD-MMM" )
),
"GREEN",
IF (
MAX ( 'Table'[Column1] )
IN SELECTCOLUMNS (
'Scheduled',
"date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
),
"ORANGE"
)
)
Put the measure Value in matrix values and in the matrix visualizations formatting>Cell elements, turn on the Backgroud color option and click "fx".
Select the measure color in the below dialog.
Get the correct result:
I attach my sample below for your reference.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best regards,
Community Support Team_yanjiang
Hi @Aknas ,
According to your description, here's my solution.
Column in the matrix is of text format in my sample.
Create two measures.
Value =
IF (
MAX ( 'Table'[Column1] )
IN SELECTCOLUMNS (
'Scheduled',
"date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
)
&& MAX ( 'Table'[Column1] )
IN SELECTCOLUMNS (
'Completed',
"DATE", FORMAT ( 'Completed'[start_date_time], "DD-MMM" )
),
" ",
IF (
MAX ( 'Table'[Column1] )
IN SELECTCOLUMNS (
'Scheduled',
"date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
),
" "
)
)
Color =
IF (
MAX ( 'Table'[Column1] )
IN SELECTCOLUMNS (
'Scheduled',
"date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
)
&& MAX ( 'Table'[Column1] )
IN SELECTCOLUMNS (
'Completed',
"DATE", FORMAT ( 'Completed'[start_date_time], "DD-MMM" )
),
"GREEN",
IF (
MAX ( 'Table'[Column1] )
IN SELECTCOLUMNS (
'Scheduled',
"date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
),
"ORANGE"
)
)
Put the measure Value in matrix values and in the matrix visualizations formatting>Cell elements, turn on the Backgroud color option and click "fx".
Select the measure color in the below dialog.
Get the correct result:
I attach my sample below for your reference.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best regards,
Community Support Team_yanjiang
Thanks Yan.
The solution works Perfect!!
Thanks again
Corrected the screenshot ( desired result)
User | Count |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |