1

I am measuring the duration in minutes of various tools. The tool can run multiple times in the same day. I would like to flag a tool as "High Use" if the tool runs for more than 20 minutes in a 2 day period. This will be shown in matrix format.

For example, I want to flag Tool A as "High Use" but not flag Tool B

I'm not sure how to indicate a 2 day time period. Is this possible to do in DAX?

Date Tool Name Minutes
12/2 Tool A 10
12/2 Tool B 5
12/2 Tool C 7
12/3 Tool A 12
12/3 Tool B 6
nakgma0102
  • 11
  • 1
  • Can you explain how you want to show this measure if in your data appears the next few days for Tools? – msta42a Dec 29 '20 at 06:31

1 Answers1

1

Assuming the table name is ToolUsage and the Date column to be a DATETIME, it's possible to write a measure to compute the last two days usage for a single tool

Tool2LastDayUsage = 
IF (
    HASONEVALUE ( ToolUsage[Tool Name] ),
    VAR MaxDate = MAX ( ToolUsage[Date] )
    VAR LastTwoDays =
        CALCULATETABLE (
            VALUES(ToolUsage[Date]),
            ToolUsage[Date] > MaxDate - 2
                && ToolUsage[Date] <= MaxDate
        )
    RETURN
        CALCULATE (
            SUM ( ToolUsage[Minutes] ),
            LastTwoDays,
            ALLEXCEPT (
                ToolUsage,
                ToolUsage[Tool Name]
            )
        )
)

First we check to have a single tool selected using HASONEVALUE, then we compute the last date in the current selection and we use it to prepare the LastTwoDays table containing the 2 days period. At last we compute the last two days usage by applying the LastTwoDays filter table, together with ALLEXCEPT to remove any existing filter over the ToolUsage table but the filter over the Tool Name

Then we can use this measure to build another measure to check the last two days period usage and flag high used tools with "High Use"

HighUse = IF( [Tool2LastDayUsage] > 20, "Hig Use" )

These measures can be used in a matrix or a table visuals. If the ToolUsage[Date] is included, they use it to compute the last two days period.

Power BI visuals with result

Of course, different behavior may be implemented; for instance to flag the tool regardless of the date on the current visual row, using the overall max date instead.

To show a 2 period inside a visual, it may be possible to create a table with a description, like 12/2-12/3 and a date, 12/3 in this case, to be set in relationship with the ToolUsage table.

sergiom
  • 4,791
  • 3
  • 24
  • 32