problem with SWITCH function

[hanswittoeck]

can somebody explain why we see here '+ 1 day'? the value is cleary above 10080, so I expect to see '+1 week'

[MFelix · Accepted solution]

Hi @hanswittoeck ,

Is this a metric or a calculated column? Taking into account the code you show I assume this is a calculated column so the values are calculated for each row of the table, I assume that each notification nº can have more than one line in your table, with small values.

Create new measure and use the same syntax but instead of:

'Operations F_PM_Notification_Details'[Duration in Minutes]

use 
SUM('Operations F_PM_Notification_Details'[Duration in Minutes])

So your syntax will be:

Groups =
SWITCH (
      TRUE(),
      SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) >= 10080, "+1 Week", 
      SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) >= 1440, "+1 day", 
      SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) <= 15, "Max 15m", 
      SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) <= 60, "Max 1h"
  )

[lbendlin · Accepted solution]

Do you want to categorize individual transactions, or the aggregates?  For aggregates you need to materialize the result first before comparing it.

[HarishKM · Accepted solution]

@hanswittoeck Hey,
I will below to use below measure.

Groups = SWITCH ( TRUE(),
SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) >= 10080, "+1 Week",
SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) >= 1440, "+1 Day",
SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) > 60, "Max 1h",
SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) <= 60, "Less than 1h",
SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) <= 15, "Max 15m",
"Regular")

Thanks

Harish KM

If above step's solved your problem. Kudos will be highly apprieciated. Accept this as solution as well

[hanswittoeck]

the problem was in my agregation. if a same notification has multiple occurances, it shows the sum. In reality it should show only 1 value, not the sum.

[lbendlin · Accepted solution]

Do you want to categorize individual transactions, or the aggregates?  For aggregates you need to materialize the result first before comparing it.

[MFelix · Accepted solution]

Hi @hanswittoeck ,

Is this a metric or a calculated column? Taking into account the code you show I assume this is a calculated column so the values are calculated for each row of the table, I assume that each notification nº can have more than one line in your table, with small values.

Create new measure and use the same syntax but instead of:

'Operations F_PM_Notification_Details'[Duration in Minutes]

use 
SUM('Operations F_PM_Notification_Details'[Duration in Minutes])

So your syntax will be:

Groups =
SWITCH (
      TRUE(),
      SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) >= 10080, "+1 Week", 
      SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) >= 1440, "+1 day", 
      SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) <= 15, "Max 15m", 
      SUM('Operations F_PM_Notification_Details'[Duration in Minutes]) <= 60, "Max 1h"
  )