Forum Discussion

TK12345's avatar
TK12345
Icon for Resolver II rankResolver II
4 years ago
Solved

RANKX with repeated values

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. 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 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

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      TK12345
      Icon for Resolver II rankResolver II
      4 years ago

      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.