Forum Discussion
Need Help with Dax (Display Occurrence Number In Calculated Column)
Hello,
Say i have a table with 2 columns and dates out of order like so
| Date | Color |
| 1/1/2017 | blue |
| 2/5/2017 | blue |
| 1/24/2017 | blue |
| 6/25/2017 | red |
| 9/1/2017 | red |
| 4/25/2017 | red |
| 3/26/2017 | green |
| 2/22/2017 | green |
| 9/24/2017 | green |
| 5/26/2017 | blue |
I want to be able to create a 3rd calculated column that will give me this...
| Date | Color | Occurrence Number |
| 1/1/2017 | blue | 1 |
| 2/5/2017 | blue | 3 |
| 1/24/2017 | blue | 2 |
| 6/25/2017 | red | 2 |
| 9/1/2017 | red | 3 |
| 4/25/2017 | red | 1 |
| 3/26/2017 | green | 2 |
| 2/22/2017 | green | 1 |
| 9/24/2017 | green | 3 |
| 5/26/2017 | blue | 4 |
Thanks for the help!
Hey,
the DAX statement to create the calculated column looks like this:
Rank by Date in group color = RANKX ( FILTER ( 'Table2' ,'Table2'[Color] = EARLIER ( Table2[Color] ) ) ,'Table2'[Date] , ,ASC ,DENSE )If creating a calculated column you have to be aware that calculated value will not change if for example you apply a filter to the rowset, meaning: if you select May (eg by using a slicer), the value of the column will still show 4 for the color blue on the 26th of May 2017. even if there is now just one row.
Hopefully this is what you are looking for
Regards
Tom
3 Replies
Hey,
the DAX statement to create the calculated column looks like this:
Rank by Date in group color = RANKX ( FILTER ( 'Table2' ,'Table2'[Color] = EARLIER ( Table2[Color] ) ) ,'Table2'[Date] , ,ASC ,DENSE )If creating a calculated column you have to be aware that calculated value will not change if for example you apply a filter to the rowset, meaning: if you select May (eg by using a slicer), the value of the column will still show 4 for the color blue on the 26th of May 2017. even if there is now just one row.
Hopefully this is what you are looking for
Regards
Tom
Perfect! Thank you Tom!
this post may help :)
https://community.powerbi.com/t5/Desktop/DAX-Ranking-or-Indexing-Dates/m-p/77794
