Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

need some help with this DAX

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
DateShiftKeySortingNum
20240101DS1
20240101NS2
20240102DS3
20240102NS4
20240103DS5
20240103NS6
20240104DS7
20240104NS8
20240105DS9
20240105NS10
  • hackcrr's avatar
    hackcrr
    2 years ago

    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.

5 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    Anonymous , Make some changes in second CM 

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

  • hackcrr's avatar
    hackcrr
    Icon for Memorable Member rankMemorable Member
    2 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      hackcrr 

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

      • hackcrr's avatar
        hackcrr
        Icon for Memorable Member rankMemorable Member
        2 years ago

        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.