Forum Discussion

Joe_T's avatar
Joe_T
Regular Visitor
2 years ago

Title: Seeking Help: Creating Dynamic Measures Based on Time Intervals

Hi Power BI Community, I'm reaching out to seek assistance with creating dynamic measures in Power BI based on time intervals. Context: I have a dataset named "TIM Tracker (New)" containing informa...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

HI Joe_T,

I'd like to suggest you create a table with time ranges based on interval. 

NewTable =
GENERATESERIES ( TIME ( 0, 0, 0 ), TIME ( 23, 59, 59 ), TIME ( 0, 15, 0 ) )

Then you can use this on the matrix column fields and write a measure expression to check the current time value and return flag if the current value is included in the time ranges that defined on your table start, end time fields.

Flag =
VAR currTime =
    MAX ( NewTable[Value] )
RETURN
    IF (
        currTime >= MIN ( Table1[Start Time] )
            && currTime <= MAX ( Table1[End Time] ),
        1
    )

After these steps, you can write a formula return color code based on the above flag values to use on 'conditional formatting' feature background color to highlight matched records.

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

Joe_T's avatar
Joe_T
Regular Visitor
2 years ago

Good Morning,

Thanks for your reply, i have created the interval table and the measure (creating the measure within the Task table). I haven't created a relationship between the two whihc might be the issue but  just wanted to share the below display, as the flag measure is returning blanks for every task line as per the below:

Please let me know if you know why that is.

Many Thanks,

Joe