Need to filter a rankx function

[Anonymous]

Hi, 

I try to add a filter on my rankx function but it does not work properly.

The filter on my page is called "Year_selection", it's not a slicer but a single selection :

Then the data in my report are based on the accident's year.

I want to make the link between my year_selection and the data displayed in the report. That is to say, if it is selected "2019" in the year_selection, i want my report displaying rank 1(=2019) and 2(=2018) (i have 2 different pies for current year and previous year).

For the moment, I only have the rank for accident year but it is not linked to the year_selection, it always remains on : 

 whatever the yearname selection.

I would like to have this if i select 2019 :

ranking|annee|yearselected

3          |2017  |2019

2          |2018  |2019

1          |2019  |2019

basically, I would like to add a filter "where annee <= max_year_selected" in my rankx formula.

Here is my basic rankx formula, I've tried lots of complex one but I think it should be easier so maybe you could help me, that would be much appreciated because it makes me crazy ^^

Annee_Ranking_Lesions = RANKX(ALLSELECTED(FactSecuriteAuTravail_Lesions[Annee]),CALCULATE(max(FactSecuriteAuTravail_Lesions[Annee])))

Thanks a lot for your help, AnSo

[harshnathani]

Hi @Anonymous ,

Please share sample pbix file and expected output.

Regards,

HN

[Anonymous]

Hi HN, 

Thanks for your answer. However, I don't know how to share my pbix, not sure if I can because data can only be access from my company 😕 please advise if there is a way to share the model, data are not confidential, just access restricted.

But I will try to explain what I want to achieve and that makes me crazy for a few days now ^^

Here is a screen shot of my model to explain : 

So I have 4 fact tables :

- SecuriteAuTravail

- FactSecuriteAuTravail_Lesions

- FactSecuriteAuTravail_Categ

- FactSecuriteAuTravail_MoyLoc

There is a field "Annee" in each table. There is also a field "Month" in each table but I use the filtering by month only for SecuriteAuTravail table. The 3 other fact tables are filtered on year only in the report.

As you can see, there is also a dimension table HierarchieService which is linked to the 4 fact tables, it is just to get hierarchy and labels of the different levels of services in the company. This one creates problem of ambiguous path, you will see in my explanations below.

Today, what I have in the report is : 

1/ Tabs linked to "SecuriteAuTravail", all with a synchronized date filter, one filter on year, one filter on month (period to be exact). This is done with a filter on Calendrier[AnnéeSlicer] for the year and Calendrier[PériodeSlicer] for the period of month :

2/ tab linked to FactSecuriteAuTravail_Lesions where the year slicer is directly based on FactSecuriteAuTravail_Lesions[Annee]:

3/ tab linked to FactSecuriteAuTravail_Categ where the year slicer is based on FactSecuriteAuTravail_Categ[Annee]

4/ tab linked to FactSecuriteAuTravail_MoyLoc where the year slicer is based on FactSecuriteAuTravail_MoyLoc[Annee]

What I would like is to have a single year filter in the report that would be synchronised between the different tabs. Or at least, keeping the date filter on year+month for SecuriteAuTravail table but having one common year filter for the 3 Lesions/Categ and MoyLoc tables.

As you can see in my model, I have tried to import a new calendar table (calendrier_lesionscategloco), I have also added a "numero_year" table between calendrier_lesionscategloco and my fact table to get single values for the available years : 

Then I thought I could link the field Numero_year[Yearname] to each year field in my 3 fact tables, putting a bidirectional relationship.

But I can't 😕 I can only link with one fact table, then I got an error message of ambiguity for the 2nd one because of the HierarchieService table which is linked to the 4 fact tables : 

Solving this issue would be the first step, I will then have some small issues to fix with rankx function in the report if I change my year filter (slider today) by a single selection, but when I will be fixing this, I will be happy, that would mean I have a synchronized date filter in all my tabs!  So I thought the problem should be explained in its globality before wanting to work on the rankx function, it will be in a second time.

Let me know if it's not clear and if you need more details to figure it out.

Thanks a lot for your help, AnSo

[amitchandak]

@Anonymous , push you to filter to calculate.

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

for exclude

https://community.powerbi.com/t5/Desktop/RANKX-excluding-blanks/m-p/397656#M181491

[Anonymous]

Hi amitchandak,

Thanks a lot for your answer. However, i've already been through many forums and tutorials, unfortunately not finding the solution to my problem. Could you be more specific to my case if possible ?

Thanks a lot, AnSo