Count of mixed values(text-integer)

[tyxanu]

Hi,

Can anyone help me achieve the below? 

I have these values in my PBi columns with my logic applied: for case type A I am counting hours passed till now, for case type B/C I am counting days passed till now.

Case | Latest comment date | Case type | Time passed till now

1 | 03-18-2022 09:56:36 AM | A | 97 hours

2 | 03-19-2022 15:35:23 PM | B | 3 days

3 | 03-21-2022 20:31:05 PM | C | 1 days

My desired output would be to count for all of these cases - how many are older than 1 week?

My DAX below returns incorrect results like: "303 hours < 168 hours: Yes", "5 days < 7 days: No"

IF('Source'[Case type]="A" && 'Source'[Time passed till now]>168 & " hours", "Yes","No")

IF('Source'[Case type]="B" && 'Source'[Time passed till now]>7 & " days", "Yes","No") 

IF('Source'[Case type]="C" && 'Source'[Time passed till now]>7 & " days", "Yes","No") 

Thank you!

[v-yalanwu-msft · Accepted solution]

Hi, @tyxanu ;

My DAX below returns incorrect results like: "303 hours < 168 hours: Yes", "5 days < 7 days: No"

IF('Source'[Case type]="A" && 'Source'[Time passed till now]>168 & " hours", "Yes","No")

IF('Source'[Case type]="B" && 'Source'[Time passed till now]>7 & " days", "Yes","No") 

IF('Source'[Case type]="C" && 'Source'[Time passed till now]>7 & " days", "Yes","No") 

---

You could create a column :

column = 
var _num=CONVERT( LEFT([Time passed till now], SEARCH(" ",[Time passed till now])),INTEGER)
return IF(('Source'[Case type]="A"&&_num>168)||
         (('Source'[Case type]="B"||'Source'[Case type]="C") &&_num>7),
         "Yes","No")

The final output is shown below:

---

My desired output would be to count for all of these cases - how many are older than 1 week?

---

You could create a measure.

count = 
CALCULATE(COUNT([Case type]),FILTER(ALLSELECTED(Source),DATEDIFF([Latest comment date],NOW(),HOUR)>7*24))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@tyxanu , My advice would be duplicate and split this column in power query. You will get a nueric column with hour. Then you can easily apply this logic

Split Column Power Query: https://youtu.be/FyO9Vmhcfag

[tyxanu]

This would be a way, but I don't want to overload the report. I am pretty sure a proper DAX similar to mine can be written.

Anyone?