Filter on RANKX not working

[scaballerom]

Hi,

I have the following ranking:

RankStartingTimeDeviation =

IF (

    HASONEVALUE('Fact Jobs'[job_name]) && [NumberOfExec]=1,

VAR LookupTable =

    ADDCOLUMNS(ALLSELECTED('Fact Jobs'),"@Deviation",[StartingTimeDeviation])

VAR CurrentValue = CALCULATE([StartingTimeDeviation], 'Fact Jobs'[job_key] = SELECTEDVALUE('Fact Jobs'[job_key]))

VAR Result =

    RANKX (LookupTable, [@Deviation], CurrentValue,DESC,Dense)

RETURN

    Result

    )

where:

StartingTimeDeviation =

VAR StartTime = SELECTEDVALUE('Fact Jobs'[job_starting_time])

Var AvgStartTime = TIMEVALUE([AvgStartingTimeR6M])

VAR Result = IF (AvgStartTime> StartTime, -DATEDIFF(StartTime,AvgStartTime,MINUTE), DATEDIFF(AvgStartTime,StartTime,MINUTE))

RETURN

    Result

and

AvgStartingTimeR6M =

VAR NumberOfDays = 181

VAR MaxDay = MAX('Fact Jobs'[job_starting_datetime])

VAR MinDay = MaxDay-NumberOfDays

VAR FilterContext =

    FILTER (

            ALL('Fact Jobs'),

            'Fact Jobs'[job_status]="Completed" &&

            'Fact Jobs'[job_key] = SELECTEDVALUE('Fact Jobs'[job_key]) &&

            'Fact Jobs'[job_starting_datetime] <= MaxDay && 'Fact Jobs'[job_starting_datetime] > MinDay)

VAR Result =

    CALCULATE(FORMAT(AVERAGE('Fact Jobs'[job_starting_time]),"HH:mm:ss"),FilterContext)

RETURN

    Result

and 

NumberOfExec = CALCULATE(COUNT('Fact Jobs'[job_key]),REMOVEFILTERS('Fact Jobs'[job_starting_time]), KEEPFILTERS('Fact Jobs'[job_starting_date]))

The problem I have is that when I try to filter on RankStartingTimeDeviation, all my rows turn blank.

Could someone help me out?

Thanks in advance for your help!

BR,
Sara

[v-zhangti]

Hi, @scaballerom 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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