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.
I am working on a covid-19 report of suspected and confirmed cases for my company contracts, and I need to count the amount of cases for each day, based on LastUpdate.
My dataset looks like this
LastUpdate | Contract | EmployeeName | Suspected | Confirmed |
01/04/2020 | A | EmployeeA | No | No |
01/04/2020 | A | EmployeeB | No | No |
01/04/2020 | A | EmployeeC | No | No |
01/04/2020 | B | EmployeeD | Yes | No |
01/04/2020 | B | EmployeeE | No | No |
01/04/2020 | C | EmployeeF | No | No |
01/04/2020 | C | EmployeeG | No | No |
01/04/2020 | C | EmployeeH | Yes | No |
02/04/2020 | A | EmployeeA | No | No |
02/04/2020 | A | EmployeeB | Yes | No |
02/04/2020 | A | EmployeeC | No | No |
02/04/2020 | B | EmployeeD | Yes | No |
02/04/2020 | B | EmployeeE | No | No |
03/04/2020 | A | EmployeeA | No | No |
03/04/2020 | A | EmployeeB | Yes | No |
03/04/2020 | A | EmployeeC | No | No |
03/04/2020 | B | EmployeeD | Yes | No |
03/04/2020 | B | EmployeeE | No | No |
03/04/2020 | C | EmployeeF | No | No |
03/04/2020 | C | EmployeeG | No | No |
03/04/2020 | C | EmployeeH | No | Yes |
04/04/2020 | B | EmployeeD | Yes | No |
04/04/2020 | B | EmployeeE | No | No |
04/04/2020 | C | EmployeeF | No | No |
04/04/2020 | C | EmployeeG | No | No |
04/04/2020 | C | EmployeeH | No | Yes |
and I'm trying to output this
Date | Suspected | Confirmed |
01/04/2020 | 2 | 0 |
02/04/2020 | 3 | 0 |
03/04/2020 | 2 | 1 |
04/04/2020 | 2 | 1 |
Not all contracts report everyday, in this case I need to count the last date it has updated.
Like above where on 02/04, contracts A and B reported 2 suspected cases + contract C update from 01/04 with 1 suspected case.
I don't now how to aproach this, can you help me?
OK, this one was tricky, but I believe I have it, see attached PBIX, the basic pattern is:
Suspected Measure =
VAR __Date = MAX('Table'[LastUpdate])
VAR __Table =
ADDCOLUMNS(
SUMMARIZE(
FILTER(ALL('Table'),[LastUpdate]<=__Date),
[Contract],
"LastDate",MAX('Table'[LastUpdate])
),
"Suspected",COUNTROWS(FILTER(ALL('Table'),'Table'[Contract]=EARLIER([Contract]) && [LastUpdate] = [LastDate] && [Suspected]="Yes"))
)
RETURN
SUMX(FILTER(__Table,[LastDate] <= __Date),[Suspected])
Thanks Greg it worked perfectly!
But it didn't work with filters the way I thought... and I guess I should have mentioned it on the post, I'm sorry.
I have a separated table with all Contracts, with a Management column wich I use on a slicer.
Like this:
Contract | Management |
A | M1 |
B | M1 |
C | M2 |
When I use the slicer, instead of calculating the number of suspected/confirmed cases of that management, it is showing the whole number for the days in wich the contracts of that management have updated.
How do I change it?
Hi @BlackBird ,
Replace ALL() with ALLSELECTED() in the measures.
Suspected Measure =
VAR __Date =
MAX ( 'Table'[LastUpdate] )
VAR __Table =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( ALLSELECTED ( 'Table' ), [LastUpdate] <= __Date ), ------changed
[Contract],
"LastDate", MAX ( 'Table'[LastUpdate] )
),
"Suspected", COUNTROWS (
FILTER (
ALLSELECTED ( 'Table' ), --------------changed
'Table'[Contract] = EARLIER ( [Contract] )
&& [LastUpdate] = [LastDate]
&& [Suspected] = "Yes"
)
)
)
RETURN
SUMX ( FILTER ( __Table, [LastDate] <= __Date ), [Suspected] ) + 0
Confirmed Measure =
VAR __Date =
MAX ( 'Table'[LastUpdate] )
VAR __Table =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( ALLSELECTED ( 'Table' ), [LastUpdate] <= __Date ), --------changed
[Contract],
"LastDate", MAX ( 'Table'[LastUpdate] )
),
"Confirmed", COUNTROWS (
FILTER (
ALLSELECTED ( 'Table' ), --------------------------------changed
'Table'[Contract] = EARLIER ( [Contract] )
&& [LastUpdate] = [LastDate]
&& [Confirmed] = "Yes"
)
)
)
RETURN
SUMX ( FILTER ( __Table, [LastDate] <= __Date ), [Confirmed] ) + 0
Best Regards,
Icey
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |