RANKX ignore empty cells

[Strueddi]

Hey Guys

I am trying to build a rank of my table "Gesamt" . I want to know which values are the newest and second newest of "Sum of 7" for each player. 

I tried now to build a rank for Diff Date (as the difference between the "date" and today. As I don't want the smallest Diff Date if Sum of 7 is empty I made a measure

DIFF DATE SF = CALCULATE(AVERAGE(Gesamt[DIFF Date]),Gesamt[Blank ID]=0)

and the rank as the following

Rank SF = RANKX(ALLEXCEPT(Gesamt,Gesamt[Name Code]),[DIFF DATE SF],,ASC,Dense)

Like that, every date with a value for Gewicht but without a value for sum of 7 is ranked as 1. I want to ignore these values and only rank if there are values for sum of 7. "Rank ID" is an indicator if sum of 7 has values or not. 

Could anyone help?

Thanks a lot. 

Sarah

Name CodeDatumGewichtSum of 7Diff DateBlank ID

Person 1	08.03.2024	80		0	1
Person 2	08.03.2024	55	60	0	0
Person 3	08.03.2024	90	70	0	0
Person 4	08.03.2024	77	80	0	0
Person 1	08.02.2024	79	60	29	0
Person 2	08.02.2024	53		29	1
Person 3	08.02.2024	89		29	1
Person 4	08.02.2024	76		29	1
Person 1	08.01.2024	81	80	60	0
Person 2	08.01.2024	52		60	1
Person 3	08.01.2024	88	60	60	0
Person 4	08.01.2024	75	55	60	0

[Strueddi · Accepted solution]

I solved it on my own...it was e bug in the "diff Date", because when the date is "today" all the calculations don't work because diff date is "0". 

So with DIFF Date = Today () - Datum +1 everything is working out well now. 

Thanks for your support.

[AmiraBedh]

With the FILTER(), I tried to consider rows where "Blank ID" equals 0, effectively ignoring rows where "Sum of 7" is empty.

Rank SF = 
RANKX(
    FILTER(
        ALLEXCEPT(Gesamt, Gesamt[Name Code]),
        Gesamt[Blank ID] = 0
    ),
    [DIFF DATE SF],
    ,
    ASC,
    Dense
)

[Strueddi]

Hi Amira

thank you for your response and your help. If I use your code, I don't see any change to the ranking I did before. There are still dates with no values for "sum of 7" with the rating 1. 

This is the "original" data set for one player with "2Rank SF" as your code. 

Do you have another idea?

[AmiraBedh]

Can you provide textual input data ?

[Strueddi]

What do you mean with "textual input data"? Sorry, I am quite new in that Power BI context.