Conditional Formatting - Highlighting Time Difference between two scan time 30+ min apart for aUser

[minishshah]

Hello,

I need help highlighting time difference between two scan time if it exceeds 30 minutes or greater per user using the conditional formatting within Power BI visualization.

The following data comes from one table, Table1. As you visualize the elements, I am mainly concern with two scan time BOLDed with RED letter which the time difference exceeds 30 minutes for a user name Doris. Hence, I would like to highlight those two scan times show the discrepancies of the alloted time if it is for the same user. In another words, if Doris has a scan time of 9/16/2021 6:00 AM, then a user name James has an entry listed right after Doris's entry with the scan time of 9/16/2021 6:40 AM, I do not need those two entries highlighted. I only want to highlight those entries with time difference for Doris only in this instance. I hope I am making the conditions here clear, but please let me know if you have any further questions. 

I look forward to acquiring needed asssistance in this manner.

RE: Here are previous links on subject with different conditions - Help with Time difference between multiple rows , Highlight specific rows in a table visual that meets a condition I have read both of these, but have not been successful in acquiring a solution for my problem.

[v-yetao1-msft]

Hi @minishshah 

(1)Add a calculated for field [scan time] .

highlight 2 = IF('Table'[time diff]>30,"yellow")

(2)Then configure conditional formatting(Background color) for field [scan time] .

Please refer to my pbix file .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yetao1-msft]

Hi @minishshah 

Is this the result you want ?

I create a sample with some simple data .

Original data :

(1)Calculate the time difference based on username grouping by adding a column .

time diff = DATEDIFF(CALCULATE(MAX('Table'[scan time]),FILTER(ALLEXCEPT('Table','Table'[username]),'Table'[scan time]<EARLIER( 'Table'[scan time]))),'Table'[scan time],MINUTE)

(2)Create a column to judge whether the time diff is greater than 30 min , if yes return “red” .

highlight = IF('Table'[time diff]>30,"red")

(3)Set conditional formatting for the field [username] . Choose Field value in Format by , highlight in Based on field .

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[minishshah]

@v-yetao1-msft ,

This is great! I was looking for exactly the same. There is another caviat i would like to add to this which I only need to highlight entire row with all rules above ONLY IF the Pallet ID is same. In another words, if the time difference exceed 30 minutes, but if that is due to change of Pallet ID, then I do not want to highlight it. Please let me know if you need any further clarification. 

[minishshah]

Also, how do create another highlight calculated column which allow to highlight the scan time entry prior the scan time entry that exceeds 30+minutes. 

For instance, In your example pbi file, where you show the first two entries for Username "B" with scan time of 1/1/2021 8:20 and then next line shows 1/1/2021 8:55, I would also like to highlight 8:20 along with 8:55. For Second example, I added Username C to show another example on this. As you see first entry for Username "C" with scan time of 1/1/2021 11:00 and then next line shows 1/1/2021 11:40, so in this case, I would like to highlight 11:00 along with 11:40. 

[lbendlin]

Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good).