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
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
Solved! Go to Solution.
Hi @Anonymous - try this
Global Confirmed =
SUMX (
SUMMARIZE (
FILTER ( 'Cases', 'Cases'[Status] = "Confirmed" ),
"MaxConf", MAX ( 'Cases'[Value] )
),
MAX ( [MaxConf] )
)
Hope this helps
David
Hi David
Thank you for your reply! It works!
Many thanks!
Jane
Hi @Anonymous - try this
Global Confirmed =
SUMX (
SUMMARIZE (
FILTER ( 'Cases', 'Cases'[Status] = "Confirmed" ),
"MaxConf", MAX ( 'Cases'[Value] )
),
MAX ( [MaxConf] )
)
Hope this helps
David
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.
User | Count |
---|---|
37 | |
36 | |
30 | |
29 | |
21 |
User | Count |
---|---|
61 | |
41 | |
32 | |
18 | |
16 |