Forum Discussion
RANKX with repeated values
- Anonymous4 years ago
Hi TK12345 ,
I have updated your sample pbix file(see attachment), please check whether that is what you want. You can create a calculated column as below:
New_Countries worked in = CALCULATE ( DISTINCTCOUNT ( 'Blad1'[Country-legal] ), FILTER ( ALLEXCEPT ( 'Blad1', 'Blad1'[Employee] ), 'Blad1'[Month/Year] <= EARLIER ( 'Blad1'[Month/Year] ) ) )Best Regards
Hi there,
thanks for your reply! I will provide the dummy data with my expected outcome right here.
I would like to see in how many countries different employees have worked, based on Month/Year, so I can filter on Month Year. As you can see in the picture I already tried a few things but it does not work out yet.
First column is my employee, second the month year and the third country name. The outcome i created myself is that when the country changes, it give RANKX +1, BUT when someone goes back to a country he worked in before, it may not show +1 but +0. I attached the Excel file as well, but as you can see in the screenshot down here, it needs to show 1 till 3 instead of 1 to 4.
As you can see, the employee goes back to the Netherlands in 01-06-2020, so PowerBI thinks the country changed because it goes from Denmark to the Netherlands, what I would like to see are some other 2's. Because he worked in the Netherlands before.
I need to see this per line because when we filter on 01-07-2021 I need to see that employee 833 worked in 3 different countries, and not 4 as I have in my dummy data. Hope this is clear enough, and someone can help me.
- Anonymous4 years agoNot applicable
Hi TK12345 ,
I have updated your sample pbix file(see attachment), please check whether that is what you want. You can create a calculated column as below:
New_Countries worked in = CALCULATE ( DISTINCTCOUNT ( 'Blad1'[Country-legal] ), FILTER ( ALLEXCEPT ( 'Blad1', 'Blad1'[Employee] ), 'Blad1'[Month/Year] <= EARLIER ( 'Blad1'[Month/Year] ) ) )Best Regards
- TK123454 years ago
Resolver II
This is exactly the way I would love to see it. I am really thankfull for your help, will accept it as solution. Thanks for the help~!!!