Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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   O...
Interesting Links
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)

     

     

    Pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EbTiAZpmovJGph_ov9RfsMEBRFTgc_s9yybwabeV4I2vKQ?e=stKYvs

     

    Paul Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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)

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks Greg_Deckler  will try that

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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!!

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      OK, Anonymous I hadn't looked at the data and I'm lazy so I didn't test anything. Some syntax errors to be sure. Did this:

       

      Days since 100th case column = 
  VAR __Table = FILTER('time_series_19-covid-Confirmed',[Country/Region] = EARLIER([Country/Region]))
  VAR __Date = 'time_series_19-covid-Confirmed'[Period]
  VAR __Table1 = 
      ADDCOLUMNS(
        __Table,
        "__Cases",
        SUMX(FILTER(__Table,[Period] <= EARLIER([Period])),[Value])
      )
  VAR __100Day = MINX(FILTER(__Table1,[__Cases] >= 100),[Period])
  VAR __Days = (__Date - __100Day) * 1.
RETURN
  IF(__Days < 1,BLANK(),__Days)

      PBIX is attached below. You have lots of 1's due to province/state.