RANKX with repeated values

[TK12345]

Hi there,

I am struggeling with the RankX function, 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, country worked in, and in the last column named Country amount I need to see in how many countries someone have worked. 

So when the country changes it needs to add 1 to country amount. BUT when it changes back to Netherlands, it is not another country because he worked in that country before, it is not allowed to go from 2, to 3. But it needs to remain 2. 

Hope someone could help me. 

[TK12345]

I am struggeling with the RankX function, 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, country worked in, and in the last column named Country amount I need to see in how many countries someone have worked. 

So when the country changes it needs to add 1 to country amount. BUT when it changes back to Netherlands, it is not another country because he worked in that country before, it is not allowed to go from 2, to 3. But it needs to remain 2. 

Hope someone could help me. 

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[TK12345]

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.

[TK12345]

https://1drv.ms/x/s!ArweH82sfYtEnEitp2YFSdv2P-kb?e=gZcJ8Z

https://1drv.ms/u/s!ArweH82sfYtEnEk-Mm8T4ZfKOmS3?e=fSLP2a

[Anonymous · Accepted solution]

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

[TK12345]

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~!!!