Locked API: How to only select working hours

[Anonymous]

Hi,

I am connected to an API for our phone call statistics in the company. This means that I have dashboards showing salesmen activity on calls.

I have a column in the API named 'enter_time'. This column shows the time the call is made. I want to exclude calls that are from 17:00 to 08:00.

I don't have access to creating own tables when connected to this API.

I was hoping to filter the report by using the advanced filtering for 'enter_time' and using 'starts with' 08,09,10,11,12,13,14,15,16.

But it seems I cannot use multiple values in this field?

Could you help out? Can I use DAX? The only button I can press is 'new measure'. As mentioned, I cannot create other columns.

Thanks in advance!

/Kris

[v-yuezhe-msft]

@Anonymous,

Firstly, please change data type of the enter_time column to Time as shown in the following screenshot.

Measure = IF(MAX(Table[enter_time])<TIME(8,0,0) ||MAX(Table[enter_time])>=TIME(17,0,0),1,0)

Secondly, drag the measure to visual level filters of your visual, and set its value to 0.


Regards,
Lydia

[Anonymous]

@v-yuezhe-msft

Hi Lydia,

Thanks. As mentioned this API is locked. (see print screen). I can only press New Measure, not the other columns since this API is locked.

I guess this is not possible to solve then?

Thanks!

/Kris

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Create the following measure, then drag the measure to visual level filter of your visual and set its value to 1.

Measure = IF(LEFT(MAX(Table1[enter_time]),2) in {"08","09","10","11","12","13","14","15","16"},1,0)

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft

Hi again,

Something is not right...

Could you explain why the MAX function is needed?

I would like to see all calls starting with hour 08, 09, 10, 11, 12, 13, 14, 15, 16. Why does not below formula work? It doesn't recognize the column.

The problem I have is that when I choose several dates filtering my data I get less and less result. For example when I choose 1 day I get 20 result when I then choose 3 days I only have 10 results left. It should be the other way around that I should get more results if I choose more dates.

As you suggested I have also made a visual filter on this measure where I have chosen 0.

It feels like I'm close but it doesn't sum up to what result should be.

I also attach a step-by-step guide when changing to table instead of staples where I suddenly get correct data...

/Kris

[v-yuezhe-msft]

@Anonymous，

You would need to add functions such as Max function or FIRSTNONBLANK function to make the measure to recognize the single value of enter_time, otherwise you will get the above issue.

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft

I used your example with Left(MAX as you shown in previous post. Then I got this problem as I mention.

This is the formula I have when I get this problem.

Measure = IF(LEFT(max(RealTimeData[enter_time]);2) in {"08";"09";"10";"11";"12";"13";"14";"15";"16"};1;0)

Could it be  that all of these starting positions need to be TRUE in order to display result for each agent? {"08";"09";"10";"11";"12";"13";"14";"15";"16"}

Or do I need to have the agent column included in the formula? The report shows calls per agent.

Need some advice....

Thanks!

/Kris

[v-yuezhe-msft]

@Anonymous,

Please create a new thread about this issue. 

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft

Super! You made my day.

Thanks!

/Kris