topic Re: need some help with this DAX in DAX Commands and Tips

need some help with this DAX

Anonymous — Fri, 10 May 2024 19:23:18 GMT

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

Re: need some help with this DAX

bhanu_gautam — Sat, 11 May 2024 04:44:54 GMT

Anonymous , Make some changes in second CM

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

Re: need some help with this DAX

hackcrr — Sat, 11 May 2024 09:30:34 GMT

Hi, Anonymous

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.

Re: need some help with this DAX

Anonymous — Mon, 13 May 2024 10:52:58 GMT

hackcrr

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

Re: need some help with this DAX

Anonymous — Mon, 13 May 2024 10:53:11 GMT

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

Re: need some help with this DAX

hackcrr — Mon, 13 May 2024 11:20:37 GMT

Hi, Anonymous

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.