Count of Time in a column

[labuser1235]

HI All,

I have a table where time is calculated where it is more than 24 hours, so I convert that in number first and then multiply with 24 in the calculated column. I would like to find the count of those columns which are in between 0-24hrs, 24-48hrs and more than 48hours. Any help on this situation. I believe dax should be done on number*24 column because time above 24hours cannot be brought in a perfect way in Power BI.

Name	Time	Time to Number	Number*24
Name1	15:15:00	0.64	15.25
Name2	35:35:00	1.48	35.58
Name3	495:30:00	20.65	495.5

Thanks in advance.

[amitchandak]

@labuser1235 , Did the last solution work out for you?

[labuser1235]

Yes @amitchandak it worked out for me. 

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a table to test:

Then split [time] column into hour, minute and second:

Please try this calculated column:

Column = SWITCH(true,'Table'[Time.1]<=24&&'Table'[Time.1]>=0,"0-24",'Table'[Time.1]>24&&'Table'[Time.1]<=48,"24-48",'Table'[Time.1]>48,"48+")

Then create a measure to count them:

Measure = COUNTROWS('Table')

The result shows:

Hope this helps.

Best Regards,

Giotto Zhi

[labuser1235]

@v-gizhi-msft How did you bring time which is more than 24 hours into Power BI. When I try to bring hours >24 from excel to Power BI it just shows in number format. 

What format should we have in excel so that we can get it more than 24 hours to Power BI any suggestions on that.

[v-gizhi-msft]

Hi,

Please make this time column to text format in excel.

Best Regards,

Giotto Zhi

[labuser1235]

@v-gizhi-msft Sure, I shall try that and get back to you on this.

[vivran22]

Hello @labuser1235 

It is not clear whether you want an additional column or not for the category. In case you want an additional column, you can do this using Power Query > Add Column > Conditional Column:

In case you do not need an additional column, but a measure to calculate it:

0-24 Hours =
COUNTAX ( 'Table', IF ( 'Table'[Number*24] < 24, 'Table'[Number*24] ) )


24-48 Hours = 
COUNTAX (
    'Table',
    IF ( 'Table'[Number*24] >= 24 && 'Table'[Number*24] <= 48, 'Table'[Number*24] )
)

More than 48 = 
COUNTAX (
    'Table',
    IF ( 'Table'[Number*24] > 48, 'Table'[Number*24] )
)

This is the output you can get:

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/