Calculate Total confirmed with Johns Hopkins dataset

[Anonymous]

Hi,

I would like to create measures to calculate total confirmed/death/recovered but I am new to DAX,  and I wonder if you can help me. 🙂

As the account of confirmed/death/recovered are accumulative in Johns Hopkins's dataset , the maximum number will be the total amount.
To calculate global confirmed cases, I will need to sum up the maximum amount of cases of each country only if the status is "Confirmed". I prefer to create a measure. Can anyone help?

Please see the sample data below:

Date (D/M/Y) Value Status DIM LOCATION.Index
02/02/2021 8258074 Recovered 3
02/02/2021 1738768 Recovered 12
02/02/2021 226309 Death 3
02/02/2021 18559 Death 22
02/02/2021 48426 Death 12
02/02/2021 690653 Recovered 22
01/02/2021 18537 Death 22
01/02/2021 730888 Confirmed 22
01/02/2021 225099 Death 3
01/02/2021 1729999 Recovered 12
01/02/2021 686374 Recovered 22
01/02/2021 1933853 Confirmed 12
01/02/2021 8202354 Recovered 3
01/02/2021 48249 Death 12
01/02/2021 9229322 Confirmed 3
31/01/2021 47974 Death 12
31/01/2021 9204731 Confirmed 3
31/01/2021 1927239 Confirmed 12
31/01/2021 18452 Death 22
31/01/2021 727109 Confirmed 22
31/01/2021 224504 Death 3
31/01/2021 1721650 Recovered 12
31/01/2021 681981 Recovered 22
31/01/2021 8176373 Recovered 3
30/01/2021 1712868 Recovered 12
30/01/2021 47931 Death 12
30/01/2021 18339 Death 22
30/01/2021 9176975 Confirmed 3
30/01/2021 1922264 Confirmed 12
30/01/2021 722900 Confirmed 22
30/01/2021 677602 Recovered 22
30/01/2021 223945 Death 3
30/01/2021 8148671 Recovered 3
29/01/2021 1703459 Recovered 12
29/01/2021 47775 Death 12
29/01/2021 18257 Death 22
29/01/2021 673582 Recovered 22
29/01/2021 222666 Death 3
29/01/2021 8066603 Recovered 3
29/01/2021 718749 Confirmed 22
29/01/2021 9118513 Confirmed 3
29/01/2021 1915362 Confirmed 12

Thank you in advance.

Jane

[Anonymous]

Hi David

Thank you for your reply! It works!

Many thanks!

Jane

[dedelman_clng · Accepted solution]

Hi @Anonymous - try this

Global Confirmed =
SUMX (
    SUMMARIZE (
        FILTER ( 'Cases', 'Cases'[Status] = "Confirmed" ),
        "MaxConf", MAX ( 'Cases'[Value] )
    ),
    MAX ( [MaxConf] )
)

Hope this helps

David