Using RANKX & FILTER

[elsteshepardo]

Hi

I've got 2 tables - 1 that stores a Project request (Requests), 2 stores an assessment (Request Assessment)

I want to rank the scores from table 2 - I've got the code for this after watching some Bas videos on Youtube

RequestsAssessmentPriority = 
VAR AssessmentRank = 
RANKX(
    ALL('Request Assessment'),
    CALCULATE(SUM('Request Assessment'[TotalMinusEffort])),,DESC,Dense)

VAR Result = 
IF(MAX('Request Assessment'[TotalMinusEffort]) <> 0, AssessmentRank)

RETURN
Result

This works but I want to exclude completed requests (no point in ranking 'old' projects). I have a status column in Table 1 that has a completed value - I want to filter the ranking but exclude the completed requests. I also want to keep the ALL so that any filters applied to the visual/report aren't applied - I need to know that Request #1 is always Request #1

Table 1 and Table 2 are related with an ID column (1:1 relationship)

[elsteshepardo]

Hi @Anonymous 

Yeah, that's how the tables are in my data (albeit simplified)

I've done the same and compared <> complete and including complete:

The left table show the measure as you're written that I already have - as you can see, Rank 5 is missing - it's filtering the resulting table

The right table shows the measure without the filter and Rank 5 is shown (stage = complete)

I don't profess to understand DAX at all, but logically it makes sense to me that the RANK part of the statement needs to include the filtering part. Doing the rank on ALL, and then only filtering the results would give the results I have?

I'll re-do your example with a new query and see if I get the same results as you

Thanks

[elsteshepardo]

@Anonymous 

I've gone through the same steps as you did, set up some new tables, linked them. I added a few more rows and some other status too.

I still don't get the required results - i.e. I want the rank to exclude Completed projects

The same measure, gives me this result:

As before, you can see that some ranks are missing - 7, 8 and 12

And just to be sure, this is the measure:

RequestsAssessmentPriority = 
VAR AssessmentRank = 
    RANKX(
        ALL('Request Assessment'),
        CALCULATE(SUM('Request Assessment'[TotalMinusEffort])),
        ,
        DESC,
        Dense
    )
VAR Result = 
    IF(
        MAX('Requests'[Stage]) <> "Complete" && MAX('Request Assessment'[TotalMinusEffort]) <> 0, 
        AssessmentRank
    )
RETURN
    Result

Any ideas?

[Anonymous]

Hi @elsteshepardo 

The following testing is for your reference.

My sample:

Requests table:

Request Assessment table:

Relationship:

Create a measure as follows:

RequestsAssessmentPriority = 
VAR AssessmentRank = 
    RANKX(
        ALL('Request Assessment'),
        CALCULATE(SUM('Request Assessment'[TotalMinusEffort])),
        ,
        DESC,
        Dense
    )
VAR Result = 
    IF(
        MAX('Requests'[Status]) <> "Completed" && MAX('Request Assessment'[TotalMinusEffort]) <> 0, 
        AssessmentRank
    )
RETURN
    Result

Output:

If this sample data is structurally different from the one you are using, please provide some sample data and the desired output based on the sample data so that I can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.