Forum Discussion
Indexing based on value
Hi all,
With the corona virus threat going on I've been working with some of the data coming from Johns Hopkins University. The main page is here: https://github.com/CSSEGISandData/COVID-19
One of the charts I am trying to create is here below where they index all countries to start their day 1 from when their Confirmed cases is above 100.
If we focus particularly on the Confirmed cases CSV file (raw path accessible here: https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv) does anyone know how to recreate this using Power BI?
If you're interested in the app I've built, I have made it public and am regularly updating it with posts on Linkedin. Will soon transfer and consolidate all this to GitHub.
- Power BI app: https://app.powerbi.com/view?r=eyJrIjoiZmQ0ZjU0M2YtZTljZC00NmE4LWE3MDEtOTZlNGQwYzAyZjlhIiwidCI6ImM4ODdmZGYyLWIwYTYtNDFlMy04ZmE4LTdmMjFkMDYyZGE4NSJ9
- Articles on LinkedIn: https://www.linkedin.com/in/markmonfort/detail/recent-activity/posts/
I would really like to create the charts above as people I think would be very interested in these charts if we could make them interactive.
In the app I've built I am pulling the data in directly from the CSV listed on the JHU GitHub but happy if someone has a solution that works off directly downloading a CSV too.
Please help.
Anonymous
Something went wrong with my complex measures, so I changed the dax to create 2 calculated columns, gladly they are less complex and straightforward. Hope this is what you are looking for.
Total #cases by country by dates = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Country/Region]=EARLIER('Table'[Country/Region])),'Table'[Period]=EARLIER('Table'[Period])) Days since 100th case column = Var Date100casesbycountry = CALCULATE(MIN([Period]), FILTER('Table','Table'[Country/Region]=EARLIER('Table'[Country/Region])),FILTER('Table','Table'[Total #cases by country by dates]>=100)) Return DATEDIFF(Date100casesbycountry,[Period],DAY)Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
I didn't have time to look at the data yet, but perhaps a columm like this:
Days since 100th case column = VAR __Table = FILTER('Table',[Country] = EARLIER([Country]) VAR __Date = 'Table'[Date] VAR __Table1 = ADDCOLUMNS( __Table, "__Cases", SUMX(__Table,[Date] <= EARLIER([Date]),[Cases Per Day]) VAR __100Day = MINX(FILTER(__Table1,[Cases] >= 100),[Date]) VAR __Days = (__Date - __100Day) * 1. RETURN IF(__Days < 0,BLANK(),__Days)Thanks Greg_Deckler will try that
Hi Greg,
Built a small demo file that pulls from the raw CSV on gitbhub. You can get it off my Google Drive: https://drive.google.com/open?id=18OxFHjXiWbAiOHw0X0jUkYLS5C0iUocL
tried your formula but could not get it to work. Can you please take a look? Or anyone else who might know, feel free to take a look?
Would be great to solve this.
Thanks everyone!!
Anonymous
Something went wrong with my complex measures, so I changed the dax to create 2 calculated columns, gladly they are less complex and straightforward. Hope this is what you are looking for.
Total #cases by country by dates = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Country/Region]=EARLIER('Table'[Country/Region])),'Table'[Period]=EARLIER('Table'[Period])) Days since 100th case column = Var Date100casesbycountry = CALCULATE(MIN([Period]), FILTER('Table','Table'[Country/Region]=EARLIER('Table'[Country/Region])),FILTER('Table','Table'[Total #cases by country by dates]>=100)) Return DATEDIFF(Date100casesbycountry,[Period],DAY)Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you Anonymous and Greg_Deckler ... your posts were so timely and so helpful. I've been busy the last day and a half but the latter post fit the bill well enough.
I have used this to look at other types of cases and look at other analysis too.
cheers
Mark
