rank measure not working correct on slicers included in Remove Filters

[Pandadev]

Hi I have spent hours trying to resolve an issue I have with the measure below.

I have 3 slicers with the following fields IATA , ICAO , Organization.

When I select a single choice from these , say ICAO XXX is selected

The issue is that even though filters Org Type , Region , Country are not selected , the measure is applying those filters

so the rank value is lower than if they were not selected.

Is there a way to only include the filters for Org Type , Region , Country if the slicer is selected . 

Here is my dax measure

Rank =
VAR SelectedMetric = SELECTEDVALUE('SortMetric'[Metric])
RETURN
RANKX(
    FILTER(
        CALCULATETABLE(
            ALLSELECTED('rankings_dynamic'),
            REMOVEFILTERS(
                'rankings_dynamic'[Organization],
                'rankings_dynamic'[IATA],
                'rankings_dynamic'[ICAO]
            )
        ),
        'rankings_dynamic'[year] = MAX('rankings_dynamic'[year])
    ),
    SWITCH(
        SelectedMetric,
        "Fleet Count",        CALCULATE(MAX('rankings_dynamic'[Fleet Count])),
        "Scheduled Flights",  CALCULATE(MAX('rankings_dynamic'[Scheduled Flights])),
        "Total Flights",      CALCULATE(MAX('rankings_dynamic'[Total Flights])),
        "Total Passengers",   CALCULATE(MAX('rankings_dynamic'[Total Passengers])),
        "RPK",                CALCULATE(MAX('rankings_dynamic'[RPK])),
        "FTK",                CALCULATE(MAX('rankings_dynamic'[FTK])),
        "Rev_USD",            CALCULATE(MAX('rankings_dynamic'[Rev USD])),
        "CRM OTP",            CALCULATE(MAX('rankings_dynamic'[CRM OTP])),
        0
    ),
    ,
    DESC,
    DENSE
)

[v-kathullac]

Hi @Pandadev ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

[Pandadev · Accepted solution]

unfortunately it has not worked , so I have decided to create 2 seperate measures , and 2 report pages , so 1 is for the yearly view by all organisations , and the other is one for single organisation selection comparing by all years. This has resolved the issue. and i have just added a button to the reports to go to the relevant report. 

[v-kathullac]

Hi @Pandadev ,

Thank you for your response, and it's great to hear that your issue was resolved with an alternative Approach.

If possible, we would greatly appreciate it if you could take a moment to share the steps you followed to overcome the issue. This would be extremely helpful for other community members who might encounter a similar situation in the future.

Also, kindly consider marking your post as accepted so it remains easily accessible and visible to others in the community

Thanks.

[v-kathullac]

Hi @Pandadev ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithanya

[Pandadev]

Hi thanks for taking out time to look into this , it is currently only working when a single organisation is selected , and then it applies all the filters to it not the selected filters , so it is by Org type , Region , Country, split by year. It really needs to work on selected filters only. and if organisation is not selected then it should be like this , 

Rank =

VAR SelectedMetric = SELECTEDVALUE('SortMetric'[Metric])

RETURN

RANKX(

    FILTER(

        CALCULATETABLE(

            ALLSELECTED('rankings_dynamic'),

            REMOVEFILTERS(

                'rankings_dynamic'[Organization],

                'rankings_dynamic'[IATA],

                'rankings_dynamic'[ICAO]

            )

        ),

        'rankings_dynamic'[year] = MAX('rankings_dynamic'[year])

    ),

    SWITCH(

        SelectedMetric,

        "Fleet Count",        CALCULATE(MAX('rankings_dynamic'[Fleet Count])),

        "Scheduled Flights",  CALCULATE(MAX('rankings_dynamic'[Scheduled Flights])),

        "Total Flights",      CALCULATE(MAX('rankings_dynamic'[Total Flights])),

        "Total Passengers",   CALCULATE(MAX('rankings_dynamic'[Total Passengers])),

        "RPK",                CALCULATE(MAX('rankings_dynamic'[RPK])),

        "FTK",                CALCULATE(MAX('rankings_dynamic'[FTK])),

        "Rev_USD",            CALCULATE(MAX('rankings_dynamic'[Rev USD])),

        "CRM OTP",            CALCULATE(MAX('rankings_dynamic'[CRM OTP])),

        0

    ),

    ,

    DESC,

    DENSE

)

[v-kathullac]

Hi @Pandadev ,

can you try with below dax measure i think it will solve your issue.

Rank =
VAR SelectedMetric = SELECTEDVALUE('SortMetric'[Metric])
VAR OrgSelected =
    HASONEVALUE('rankings_dynamic'[Organization])
VAR RankContext =
    IF(
        OrgSelected,
        // When Org is selected, use all filters including Org
        CALCULATETABLE(
            ALLSELECTED('rankings_dynamic'),
            'rankings_dynamic'[year] = MAX('rankings_dynamic'[year])
        ),
        // When Org is not selected, ignore Org/IATA/ICAO filters
        CALCULATETABLE(
            ALLSELECTED('rankings_dynamic'),
            REMOVEFILTERS(
                'rankings_dynamic'[Organization],
                'rankings_dynamic'[IATA],
                'rankings_dynamic'[ICAO]
            ),
            'rankings_dynamic'[year] = MAX('rankings_dynamic'[year])
        )
    )
RETURN
RANKX(
    RankContext,
    SWITCH(
        SelectedMetric,
        "Fleet Count",        CALCULATE(MAX('rankings_dynamic'[Fleet Count])),
        "Scheduled Flights",  CALCULATE(MAX('rankings_dynamic'[Scheduled Flights])),
        "Total Flights",      CALCULATE(MAX('rankings_dynamic'[Total Flights])),
        "Total Passengers",   CALCULATE(MAX('rankings_dynamic'[Total Passengers])),
        "RPK",                CALCULATE(MAX('rankings_dynamic'[RPK])),
        "FTK",                CALCULATE(MAX('rankings_dynamic'[FTK])),
        "Rev_USD",            CALCULATE(MAX('rankings_dynamic'[Rev USD])),
        "CRM OTP",            CALCULATE(MAX('rankings_dynamic'[CRM OTP])),
        0
    ),
    ,
    DESC,
    DENSE
)

Regards,

Chaithanya.

[Pandadev]

Thanks for looking at this , I tried the code but in the last section it says that SelectedMetric is not the correct type

RANKX(

    RankContext,

    SWITCH(

        SelectedMetric,

        "Fleet Count",        CALCULATE(MAX('rankings_dynamic'[Fleet Count])),

        "Scheduled Flights",  CALCULATE(MAX('rankings_dynamic'[Scheduled Flights])),

        "Total Flights",      CALCULATE(MAX('rankings_dynamic'[Total Flights])),

        "Total Passengers",   CALCULATE(MAX('rankings_dynamic'[Total Passengers])),

        "RPK",                CALCULATE(MAX('rankings_dynamic'[RPK])),

        "FTK",                CALCULATE(MAX('rankings_dynamic'[FTK])),

        "Rev_USD",            CALCULATE(MAX('rankings_dynamic'[Rev USD])),

        "CRM OTP",            CALCULATE(MAX('rankings_dynamic'[CRM OTP])),

        0

    ),

    ,

    DESC,

    DENSE

)

[v-kathullac]

Hi @Pandadev ,

As i was unable to achieve the desired output using a measure, so I have created calculated columns for each metric instead. This approach has helped me obtain the correct ranking. Please find the screenshot attached for your reference.

Below is an example of the calculated column for Fleet Count:

Rank_FleetCount =
RANKX(
FILTER(
'Mock_Data',
'Mock_Data'[Year] = EARLIER('Mock_Data'[Year]) &&
(
NOT ISFILTERED('Mock_Data'[Org Type]) ||
'Mock_Data'[Org Type] = EARLIER('Mock_Data'[Org Type])
) &&
(
NOT ISFILTERED('Mock_Data'[Region]) ||
'Mock_Data'[Region] = EARLIER('Mock_Data'[Region])
) &&
(
NOT ISFILTERED('Mock_Data'[Country]) ||
'Mock_Data'[Country] = EARLIER('Mock_Data'[Country])
)
),
'Mock_Data'[Fleet Count],
,
DESC,
DENSE
)

You may have to  create similar calculated columns for the other metrics such as TotalFlights_Rank, TotalPassengers_Rank, RPK_Rank, FTK_Rank, Rev_USD_Rank, and CRM_OTP_Rank.

Please let me know if you need any further assistance.

Regards,
Chaithanya

[Pandadev]

Thanks for taking out time to look into this , is there a way all can be combined as I have been instructed that only 1 rank column is allowed , so I still need it work from selected rank metric.

[v-kathullac]

Hi @Pandadev ,

I just attempted to merge all the individual measures into a single consolidated measure. I'm attaching the screenshot for your reference.
Please let me know if any further adjustments are needed.

Rank 1 = 
VAR SelectedMetric = SELECTEDVALUE('SortMetric'[Metric])
VAR CurrentYear = MAX('Mock_Data'[Year])

-- Remove slicers for IATA, ICAO, Organization
VAR BaseTable =
    FILTER (
        ALL ( 'Mock_Data' ),
        'Mock_Data'[Year] = CurrentYear &&
        (
            NOT ISFILTERED('Mock_Data'[Org Type]) ||
            'Mock_Data'[Org Type] IN VALUES('Mock_Data'[Org Type])
        ) &&
        (
            NOT ISFILTERED('Mock_Data'[Region]) ||
            'Mock_Data'[Region] IN VALUES('Mock_Data'[Region])
        ) &&
        (
            NOT ISFILTERED('Mock_Data'[Country]) ||
            'Mock_Data'[Country] IN VALUES('Mock_Data'[Country])
        )
    )

-- Metric to rank
VAR ValueToRank =
    SWITCH (
        SelectedMetric,
        "Fleet Count",        CALCULATE(MAX('Mock_Data'[Fleet Count])),
        "Scheduled Flights",  CALCULATE(MAX('Mock_Data'[Scheduled Flights])),
        "Total Flights",      CALCULATE(MAX('Mock_Data'[Total Flights])),
        "Total Passengers",   CALCULATE(MAX('Mock_Data'[Total Passengers])),
        "RPK",                CALCULATE(MAX('Mock_Data'[RPK])),
        "FTK",                CALCULATE(MAX('Mock_Data'[FTK])),
        "Rev USD",            CALCULATE(MAX('Mock_Data'[Rev USD])),
        "CRM OTP",            CALCULATE(MAX('Mock_Data'[CRM OTP])),
        BLANK()
    )

-- Final rank
RETURN
IF (
    NOT ISBLANK(ValueToRank),
    RANKX(
        BaseTable,
        SWITCH (
            SelectedMetric,
            "Fleet Count",        CALCULATE(MAX('Mock_Data'[Fleet Count])),
            "Scheduled Flights",  CALCULATE(MAX('Mock_Data'[Scheduled Flights])),
            "Total Flights",      CALCULATE(MAX('Mock_Data'[Total Flights])),
            "Total Passengers",   CALCULATE(MAX('Mock_Data'[Total Passengers])),
            "RPK",                CALCULATE(MAX('Mock_Data'[RPK])),
            "FTK",                CALCULATE(MAX('Mock_Data'[FTK])),
            "Rev USD",            CALCULATE(MAX('Mock_Data'[Rev USD])),
            "CRM OTP",            CALCULATE(MAX('Mock_Data'[CRM OTP])),
            0
        ),
        ,
        DESC,
        DENSE
    )
)

Regards,

Chaithanya.

[v-kathullac]

Hi @Pandadev ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithanya

[Pandadev]

Unrfortunately the latest dax measure returned the value 1 for every row for rank , so it has still not been resolved

[Pandadev]

I have provided the dummy tables with dax table code , so hopefully the issue can be worked with actual data , I spent another yesterday trying to find a solution , but beginning to think that maybe it is not possible. Hoping that somebody can prove me wrong and find a solution. I would like say a big thankyou to everyone who has taken out time to look into this for me.

[v-kathullac]

Hi @Pandadev ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithanya

[Pandadev]

Hi , unfortunately I have not had the issue resolved yet.

[v-kathullac]

HI @Pandadev ,

Can you try with below dax measure and let us know if you face any issues.

Rank = 
VAR SelectedMetric = SELECTEDVALUE('SortMetric'[Metric])
VAR BaseTable =
    CALCULATETABLE(
        ALLSELECTED('Mock_Data'),
        REMOVEFILTERS('Mock_Data'[Organization], 'Mock_Data'[IATA], 'Mock_Data'[ICAO])
    )
VAR ConditionallyFilteredTable =
    FILTER(
        BaseTable,
        'Mock_Data'[Year] = MAX('Mock_Data'[Year])
            &&
            (
                NOT ISFILTERED('Mock_Data'[Org Type]) || 'Mock_Data'[Org Type] IN VALUES('Mock_Data'[Org Type])
            )
            &&
            (
                NOT ISFILTERED('Mock_Data'[Region]) || 'Mock_Data'[Region] IN VALUES('Mock_Data'[Region])
            )
            &&
            (
                NOT ISFILTERED('Mock_Data'[Country]) || 'Mock_Data'[Country] IN VALUES('Mock_Data'[Country])
            )
    )
RETURN
    RANKX(
        ConditionallyFilteredTable,
        SWITCH(
            SelectedMetric,
            "Fleet Count",        CALCULATE(MAX('Mock_Data'[Fleet Count])),
            "Scheduled Flights",  CALCULATE(MAX('Mock_Data'[Scheduled Flights])),
            "Total Flights",      CALCULATE(MAX('Mock_Data'[Total Flights])),
            "Total Passengers",   CALCULATE(MAX('Mock_Data'[Total Passengers])),
            "RPK",                CALCULATE(MAX('Mock_Data'[RPK])),
            "FTK",                CALCULATE(MAX('Mock_Data'[FTK])),
            "Rev_USD",            CALCULATE(MAX('Mock_Data'[Rev USD])),
            "CRM OTP",            CALCULATE(MAX('Mock_Data'[CRM OTP])),
            0
        ),
        ,
        DESC,
        DENSE
    )

Regards,

Chaithanya.

[Pandadev]

Hi there are no errors , but it returns value 1 for every row

[v-kathullac]

Thanks @Bibiano_Geraldo , @bhanu_gautam  for Addressing the issue.

Hi @Pandadev ,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya

[Pandadev]

unfortunately it did not resolve the issue , I tried to add a table of 200 rows of dummy data but it said exceeded 2000 charcter limit , so I provided 10 rows

[Bibiano_Geraldo]

Hi @Pandadev ,

To achieve your goal, please try the bellow DAX measure:

Rank =
VAR SelectedMetric = SELECTEDVALUE( 'SortMetric'[Metric] )

VAR BaseTable =
    CALCULATETABLE(
        ALLSELECTED( 'rankings_dynamic' ),

        /* Always ignore your IATA/ICAO/Organization slicers */
        REMOVEFILTERS(
            'rankings_dynamic'[Organization],
            'rankings_dynamic'[IATA],
            'rankings_dynamic'[ICAO]
        ),

        /* Dynamically remove Org Type only if NOT filtered by user */
        IF(
            NOT ISFILTERED( 'rankings_dynamic'[Org Type] ),
            REMOVEFILTERS( 'rankings_dynamic'[Org Type] )
        ),

        /* Same pattern for Region */
        IF(
            NOT ISFILTERED( 'rankings_dynamic'[Region] ),
            REMOVEFILTERS( 'rankings_dynamic'[Region] )
        ),

        /* …and Country */
        IF(
            NOT ISFILTERED( 'rankings_dynamic'[Country] ),
            REMOVEFILTERS( 'rankings_dynamic'[Country] )
        )
    )

RETURN
    RANKX(
        FILTER(
            BaseTable,
            'rankings_dynamic'[year] = MAX( 'rankings_dynamic'[year] )
        ),
        SWITCH(
            SelectedMetric,
            "Fleet Count",        CALCULATE( MAX( 'rankings_dynamic'[Fleet Count] ) ),
            "Scheduled Flights",  CALCULATE( MAX( 'rankings_dynamic'[Scheduled Flights] ) ),
            "Total Flights",      CALCULATE( MAX( 'rankings_dynamic'[Total Flights] ) ),
            "Total Passengers",   CALCULATE( MAX( 'rankings_dynamic'[Total Passengers] ) ),
            "RPK",                CALCULATE( MAX( 'rankings_dynamic'[RPK] ) ),
            "FTK",                CALCULATE( MAX( 'rankings_dynamic'[FTK] ) ),
            "Rev_USD",            CALCULATE( MAX( 'rankings_dynamic'[Rev USD] ) ),
            "CRM OTP",            CALCULATE( MAX( 'rankings_dynamic'[CRM OTP] ) ),
            0
        ),
        ,
        DESC,
        DENSE
    )