Forum Discussion
Index depending on date, category and first case
Hi everybody,
I'm still new in this and need help to index or rank my table.
Here is the situation, I have a table with a date, country, and cases. I want to index/rank by date by country but starting to count by the first appearance.
Below is an example of my data, the Index Column is the desired result, right now I do not have that column. Take US and Mexico as an example, the first case (Case column diff than 0) is on March 13th, so I want that to be the index/rank 1 and then the next day 2, next day 3, and so on but just for the US. Then Mexico the first case (case column diff than 0) is on March 19th, so I want that to be index/rank 1 for Mexico, next day 2, next day 3.
Any ideas, I found a formula using RANKX in combination with EARLIER, but that was fine, but it started counting since the first country appearance, no matter if the cases were 0, I want to start counting when it detects the first case.
| Date | Country | Cases | Index |
| Tuesday, March 10, 2020 | Mexico | 0 | |
| Tuesday, March 10, 2020 | US | 0 | |
| Wednesday, March 11, 2020 | Mexico | 0 | |
| Wednesday, March 11, 2020 | US | 0 | |
| Thursday, March 12, 2020 | Mexico | 0 | |
| Thursday, March 12, 2020 | US | 0 | |
| Friday, March 13, 2020 | Mexico | 0 | |
| Friday, March 13, 2020 | US | 5 | 1 |
| Saturday, March 14, 2020 | Mexico | 0 | |
| Saturday, March 14, 2020 | US | 0 | 2 |
| Sunday, March 15, 2020 | Mexico | 0 | |
| Sunday, March 15, 2020 | US | 12 | 3 |
| Monday, March 16, 2020 | Mexico | 0 | |
| Monday, March 16, 2020 | US | 29 | 4 |
| Tuesday, March 17, 2020 | Mexico | 0 | |
| Tuesday, March 17, 2020 | US | 39 | 5 |
| Wednesday, March 18, 2020 | Mexico | 0 | |
| Wednesday, March 18, 2020 | US | 46 | 6 |
| Thursday, March 19, 2020 | Mexico | 1 | 1 |
| Thursday, March 19, 2020 | US | 78 | 7 |
| Friday, March 20, 2020 | Mexico | 1 | 2 |
| Friday, March 20, 2020 | US | 83 | 8 |
| Saturday, March 21, 2020 | Mexico | 2 | 3 |
| Saturday, March 21, 2020 | US | 131 | 9 |
| Sunday, March 22, 2020 | Mexico | 2 | 4 |
| Sunday, March 22, 2020 | US | 138 | 10 |
| Monday, March 23, 2020 | Mexico | 3 | 5 |
| Monday, March 23, 2020 | US | 196 | 11 |
Hi Anonymous
try this
Column = VAR __filterCountry = ALLEXCEPT( 'Table', 'Table'[Country] ) VAR __firstDate = CALCULATE( MIN( 'Table'[Date] ), __filterCountry, 'Table'[Cases] > 0 ) VAR __tbl = CALCULATETABLE( 'Table', __filterCountry, 'Table'[Date] >= __firstDate ) RETURN IF( 'Table'[Date] >= __firstDate, RANKX( __tbl, 'Table'[Date],, ASC, Dense ) )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
3 Replies
- Mariusz
Community Champion
Hi Anonymous
try this
Column = VAR __filterCountry = ALLEXCEPT( 'Table', 'Table'[Country] ) VAR __firstDate = CALCULATE( MIN( 'Table'[Date] ), __filterCountry, 'Table'[Cases] > 0 ) VAR __tbl = CALCULATETABLE( 'Table', __filterCountry, 'Table'[Date] >= __firstDate ) RETURN IF( 'Table'[Date] >= __firstDate, RANKX( __tbl, 'Table'[Date],, ASC, Dense ) )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn