Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Calculate Total confirmed with Johns Hopkins dataset

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

 

1 ACCEPTED SOLUTION
dedelman_clng
Community Champion
Community Champion

Hi @Anonymous - try this

 

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

 

2021-02-24 16_51_38-scratch4 - Power BI Desktop.png

 

Hope this helps

David

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi David

 

Thank you for your reply! It works!

 

Many thanks!

 

Jane

dedelman_clng
Community Champion
Community Champion

Hi @Anonymous - try this

 

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

 

2021-02-24 16_51_38-scratch4 - Power BI Desktop.png

 

Hope this helps

David

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors