Forum Discussion

Pseifert's avatar
Pseifert
Frequent Visitor
6 years ago
Solved

Group By for calculated column

Hello,
 
I am trying to select the correct cycle speed with dax based on the MIN recipe #. My current measure below, however, does not account for an ITEM that has multiple resources. How can I adjust my calculated column to account for the resource. I can not figure out how to or if Group By is the correct function here but it seems you can not use group by and calculate. Right now, my measure looks at this first 0 and has assigned 0 for the current cycle. See picture below for expected results.
 
 
CurrentCycle =
Calculate(
SELECTEDVALUE(V_SAP_NTEC_CI_CYCLES01[CYCLE_SPEED]),
Filter(all(V_SAP_NTEC_CI_CYCLES01),(V_SAP_NTEC_CI_CYCLES01[RECIPE]=Min(V_SAP_NTEC_CI_CYCLES01[RECIPE])))
)
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    6 years ago

    Pseifert 

     

    You may use the following DAX to add a calculated column.

     

    Column =
MAXX (
    TOPN (
        1,
        FILTER (
            V_SAP_NTEC_CI_CYCLES01,
            V_SAP_NTEC_CI_CYCLES01[ITEM_ID] = EARLIER ( V_SAP_NTEC_CI_CYCLES01[ITEM_ID] )
                && V_SAP_NTEC_CI_CYCLES01[RESOURCE] = EARLIER ( V_SAP_NTEC_CI_CYCLES01[RESOURCE] )
        ),
        V_SAP_NTEC_CI_CYCLES01[RECIPE], ASC
    ),
    V_SAP_NTEC_CI_CYCLES01[CYCLE_SPEED]
)

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Pseifert,

     

    Is CurrentCycle a calculated column in your table or is it a measure? You refer to it as both.

     

    Thanks!

    • Pseifert's avatar
      Pseifert
      Frequent Visitor
      6 years ago

      Sorry, I have it as a measure now, I am unsure which would be ideal to be honest.

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Pseifert 

     

    You may use the following DAX to add a calculated column.

     

    Column =
MAXX (
    TOPN (
        1,
        FILTER (
            V_SAP_NTEC_CI_CYCLES01,
            V_SAP_NTEC_CI_CYCLES01[ITEM_ID] = EARLIER ( V_SAP_NTEC_CI_CYCLES01[ITEM_ID] )
                && V_SAP_NTEC_CI_CYCLES01[RESOURCE] = EARLIER ( V_SAP_NTEC_CI_CYCLES01[RESOURCE] )
        ),
        V_SAP_NTEC_CI_CYCLES01[RECIPE], ASC
    ),
    V_SAP_NTEC_CI_CYCLES01[CYCLE_SPEED]
)