Show errors that occur only after the latest flag

[MoOnan]

Hi,

I am making a report based on a table with some files upload statistics. Sometimes the process of files upload crashes and datasets are duplicated or some data is missing. One way to track it is to check minimum and maximum id of uploaded datasets. New dataset's minimum id should always be calculated this way: 

previous max id + 1 = next minimum id

When there is a discrepancy we reload relevant components. But sometimes error occures again after reload.

What I'm trying to achieve is that when I select error on a slicer, I want it to only show the rows that have an invalid minimum ID, but only after the last reload.

Sample data below:

Name	Dataset	Date	Min Id	Max Id	Status	Errors	ReloadFlag
AAA	D14	17.03.2023	131,00	140,00	Sent
AAA	D13	17.03.2023	133,00	140,00	Not Sent
AAA	D12	16.03.2023	103,00	130,00	Sent		Reload
AAA	D7	12.02.2023	125,00	130,00	Sent	Error
AAA	D6	09.02.2023	103,00	120,00	Sent
BBB	D15	17.03.2023	83,00	91,00	Sent
BBB	D11	16.03.2023	75,00	82,00	Sent
BBB	D9	12.03.2023	65,00	74,00	Sent	Error
BBB	D3	01.02.2023	45,00	60,00	Sent		Reload
BBB	D2	31.12.2022	51,00	60,00	Sent	Error
BBB	D1	27.12.2022	45,00	48,00	Sent
CCC	D10	15.03.2023	173,00	180,00	Sent	Error
CCC	D8	26.02.2023	170,00	180,00	Sent		Reload
CCC	D5	07.02.2023	170,00	175,00	Sent	Error
CCC	D4	01.02.2023	170,00	175,00	Sent

Datasets number: D13, D7, D2, D5 are the records, which had wrong minimum id, but where reloaded or not sent, therefore they aren't issues anymore and shouldn't appear in the result.

According to that, this is expected result:

Name	Dataset	Date	Min Id	Max Id	Errors	Status
BBB	D9	12.03.2023	65,00	74,00	Error	Sent
CCC	D10	15.03.2023	173,00	180,00	Error	Sent

This is what I did so far:

Previous_Value = CALCULATE(

MAX('Table'[Max ID]),

FILTER('Table','Table'[Name] = EARLIER('Table'[Name]) && 'Table'[Date] < EARLIER('Table'[Date]) && EARLIER('Table'[ReloadFlag] ) <> "RELOAD" ))

Id_Control = IF(Table[Status] <>"Sent" || Table[ReloadFlag] = "RELOAD", 0,

(IF(Table[Min Id]= (Table[Previous_Value]+1),0, 1)))

However, I don't know how to arrange it so that it will show me only errors occuring after the latest reload and won't show me errors where datasets have status "Not Sent".

I will be very grateful if someone could help me, because I am really stuck on this issue..

[v-zhangti · Accepted solution]

Hi, @MoOnan 

You can try the following methods.

Column:

ReloadDate = 
CALCULATE ( MAX ( 'Table'[Date]),
    FILTER ( 'Table', 'Table'[Dataset]=EARLIER('Table'[Dataset] )
                   && EARLIER ( 'Table'[ReloadFlag] ) = "RELOAD" ) )

ReloadNextDate = 
CALCULATE ( MIN ( 'Table'[Date] ),
FILTER (ALL ( 'Table' ), 'Table'[Name] = EARLIER ( 'Table'[Name] )
            && 'Table'[Date] > CALCULATE ( MAX ( 'Table'[ReloadDate] ),
                               FILTER ( 'Table', [Name] = EARLIER ( 'Table'[Name] )))))

Result = IF([Date]=[ReloadNextDate]&&[Errors]="Error",[Dataset],BLANK())

New Table =
FILTER (
    SUMMARIZE (
        'Table',
        'Table'[Name],
        'Table'[Result],
        'Table'[Date],
        'Table'[Max Id],
        'Table'[Min Id],
        'Table'[Errors],
        'Table'[Status]
    ),
    [Result] <> BLANK ()
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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