Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |