Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |