need some help with this DAX

[bwang2]

I have two CM

"DSKfromlastcyle =

VAR _PreviousCycle = SELECTEDVALUE(CC[CycleNum])-1

VAR _DateShiftKetAfter = CALCULATE(MAX(DimCrewSchedule[DateShiftKey]),CC[CycleNum]= _PreviousCycle)

RETURN _DateShiftKetAfter"
This CM gives me the desired outcome. But the one CM below is not.
"SortingNum =

CALCULATE(MAX(DimCrewSchedule[SortingNum]),FILTER(DimCrewSchedule,DimCrewSchedule[DateShiftKey] = [DSKfromlastcyle]))"

An example outcome of CM1 is "20240101DS" and the corresponding CM2 outcome is "1". When I run CM2, it's giving me blank, but if I mannualy type "20240101DS" insted of "[DSKfromlastcyle]", it will give me "1" as the outcome. 

Below is a portion of my DIMCrewSchedule Table

DateShiftKey	SortingNum
20240101DS	1
20240101NS	2
20240102DS	3
20240102NS	4
20240103DS	5
20240103NS	6
20240104DS	7
20240104NS	8
20240105DS	9
20240105NS	10

[hackcrr]

Hi, @bwang2 

Perhaps you could try the following dax expression:

SortingNum1 = 
VAR DSKValue = [DSKfromlastcyle]  
RETURN  
    CALCULATE(  
        MAX(DimCrewSchedule[SortingNum]),  
        FILTER(ALLSELECTED(DimCrewSchedule),
        DimCrewSchedule[DateShiftKey] = DSKValue
        )
    )

The results are as follows:

Best Regards,

hackcrr

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

[bwang2]

@hackcrr 

Thanks for helping, I tried the DAX you provided, but it still gives me blank

[hackcrr · Accepted solution]

Hi, @bwang2 

Thank you very much for your reply. You should put this metric and DateShiftKey column in table visual. See if you get the expected results. The metric needs to rely on an external context such as the DateShiftKey column to work.

Best Regards,

hackcrr

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

[bhanu_gautam]

@bwang2 , Make some changes in second CM 

SortingNum =
VAR SelectedDateShiftKey = [DSKfromlastcyle]
RETURN
CALCULATE(
MAX(DimCrewSchedule[SortingNum]),
DimCrewSchedule[DateShiftKey] = SelectedDateShiftKey
)

[bwang2]

@bhanu_gautam 
Thanks for helping, I tried the DAX you provided, but it still gives me blank