CODEWITHSUNDEEP
powerbipowerbi-desktop
Adam Andersson
Adam Andersson

Reputation: 113

Filtering reoccuring values Power BI using DirectQuery

I have a table containing timestamps and Error codes from machines.

Example of Reoccurring calculated column

The machines will sometimes repeat the same error several times in a row but i only want to count these as one error. Thus I'm looking for a way to calculate if these errors is reoccurring and filter out these errors with some kind of filter.

I'm using DirectQuery so using EARLIER() to get the last error does not seem to work.

How should i filter these reoccuring errors?

Upvotes: 0

Views: 244

Answers (2)

Adam Andersson
Adam Andersson

Reputation: 113

As Andrey Nikolov assumed i needed to use PARTITION BY clause using the serial numbers for the machines.

SELECT TOP 100 PERCENT *,
       (CASE WHEN error = 0 OR error = LAG(error, 1, 0) OVER (PARTITION BY serial_nr ORDER BY event_time DESC)
       THEN 0
       ELSE 1
       END) AS error_is_new
FROM MyTable

I added a new column in my table containing whether an error is new.

I used error_is_new to only show the errors that were new.

Upvotes: 1

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

If you want to do this in the database, Azure SQL Database supports LAG function, so the query for loading the data to Power BI could be something like this:

declare @t table([Time] time, [Error] int)
insert into @t([Time], [Error]) values
    ('11:01', 0),
    ('12:12', 0),
    ('13:31', 4),
    ('14:50', 0),
    ('15:10', 4),
    ('15:20', 4),
    ('15:30', 4),
    ('15:40', 4),
    ('17:01', 1),
    ('18:09', 1),
    ('19:41', 0)


select
    t.[Time]
    , t.[Error]
    , IIF(t.[Error] <> 0 and LAG(t.[Error], 1) OVER(ORDER BY t.[Time]) = t.[Error], 1, 0) as Reoccuring
from @t t
order by t.[Time]

enter image description here

Please note, that the example doesn't show partitioning the data, e.g. by machine or something, because your sample data doesn't include that. If you need to do it, you must add PARTITION BY clause to the LAG function. If you update your question with exact database schema, I will update my answer too.

Upvotes: 1

Related Questions