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)