Grouping of minutes in calculated column.

[matrix_user]

Hi all,

I am trying to group time results/values from a calculated column (Call Duration Difference) into another calculated column (LoT).

The grouping of time is from seconds to hours (0 -4 mins, 5 - 9 mins...90+ mins). 

The DAX I have written in the LoT column is not accepting how I want to group time except for the last criteria, “0 – 4” but for all rows:

Am I missing a Filter function of some sort given that the 0 – 4 is on all rows. I know that green text (90, 75, 60 etc) is not what I expected. What DAX can I use to fix this? I aim to use the groups in both visual axis and visual filter.

Some background info:

• The Call Duration Difference column are results from an arithmetic calculation (StartTime minus EndTime) these values are in Date/time data type.
• The Call Duration Difference column is in Time data type.
• The LoT column is in Text data type.

Any help is much appreciated.

[Anonymous · Accepted solution]

Finally sent through! Try using TIME(0,1,30) instead of 90 and etc.

[v-eqin-msft · Accepted solution]

Hi @matrix_user ,

According to my understanding, you want to create a flag column based on time duration(seconds), right?

If so, you could use [Call Duration Difference]*60*60*24 to firstly get total seconds( same for minutes —— * 60*24)

then try SWITCH() instead of nested if.

For example:

LoT =
VAR _second = [Call Duration Difference] * 60 * 60 * 24
RETURN
    SWITCH (
        TRUE (),
        _second >= 90, "90+",
        _second >= 80, "80-89",
        _second >= 60, "60-79",
        _second >= 40, "40-59",
        _second >= 20, "20-39",
        _second >= 5, "5-19",
        "0-4"
    )

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[matrix_user]

Hi... can anyone help please?

[Anonymous]

Not sure why my replies keep disappearing...

[Anonymous · Accepted solution]

Finally sent through! Try using TIME(0,1,30) instead of 90 and etc.

[matrix_user]

I tried replicating your suggestion but nothing (blue cover is "Table 1") :

The red lines shows minutes over 4 mins but still grouped into the "0 - 4" LoT.

[Anonymous]

What data type does your Call Duration uses, mine is Time with hh:nn:ss Format, is it possible to change it?

[matrix_user]

Hi YongChen, mine is the same.