Forum Discussion

davidpOuray's avatar
davidpOuray
Frequent Visitor
9 years ago
Solved

Issues with aggregating a measure.

We have a production floor report that essentially takes timeclock data and compares it to set standard minutes to generate efficiency ratings per employee.   The timeclock data is manually importe...
Need Help
  • davidpOuray's avatar
    davidpOuray
    9 years ago

    Progress. So using RELATED(EmplTableAX[Shift]) let it work in a calculated column, and it looks like its agreggating it properly...BUT it no longer works with the SP NET/GROSS measures since apparently one can't use a calculated column in a measure??

     

    UPDATED: I got the SP NET/GROSS % measures to work by using SUM(Est Min), it will only work if its summing, not just the raw column reference. I think its all working now!

     

    Thank you for your help Henry!

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

davidpOuray

 

How about the result if we change the "Est Min" measure to column with following formula?

 

Est mins Column = 
IF (
    MAX ( Events[Date] ) = TODAY (),
    SWITCH (
        EmplTableAX[Shift],
        1, MIN ( 480, 24 * 60 * ( NOW () - ( TODAY () + TIME ( 5, 0, 0 ) ) ) ),
        2, MIN ( 600, 24 * 60 * ( NOW () - ( TODAY () + TIME ( 13, 45, 0 ) ) ) )
    ),
    SWITCH ( EmplTableAX[Shift], 1, 480, 2, 600 )
)

 

Best Regards,

Herbert

davidpOuray's avatar
davidpOuray
Frequent Visitor
9 years ago

Thanks Herbert!

 

I tried out  your formula and I get this:

"A single value for column 'Shift' in table 'EmplTableAX' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

This is likely why I had the SUMX in there (then it would only sum per row and there would be only 1 value of Shift then), but that obviously doesn't work correctly either. 

 

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    davidpOuray

     

    According to the message you got, it seems that you used the formula to create a measure. Could you please try to create a calculated column with above “Est mins Column” expression?

     

    Best Regards,

    Herbert

    • davidpOuray's avatar
      davidpOuray
      Frequent Visitor
      9 years ago

      Creating a column with the same DAX gave the same error.

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        9 years ago

        davidpOuray

         

        Could you please share your .pbix file to me with OneDrive? I can help to troubleshoot it with the file.

         

        Best Regards,

        Herbert