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.

     

Anonymous's avatar
Anonymous
Not applicable
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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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