Locked API: Need to show only working hours in statistics

[Anonymous]

Hi,

Referring to ticket: https://community.powerbi.com/t5/Desktop/Locked-API-How-to-only-select-working-hours/m-p/517266

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. The API is locked so the only thing I can do is to create new measure.

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.

I got the following advice from Power BI support;

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)

I thought this solved my headache but I found problems with the formula;

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.

As you can see from my step-by-step guide I suddenly get correct data when adding the enter_time column to the table. But without the enter_time column the values are not correct.

Could the problem 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"}?

Meaning; an agent needs to have made calls all these hours ({"08";"09";"10";"11";"12";"13";"14";"15";"16"}) in order to be visible in the statistics?

Thanks!

/Kris

[v-lili6-msft]

hi, @Anonymous

After my test, You may try this measure as below:

Measure 2 = IF(HOUR(MAX(Table1[time])) in {8,9,10,11,12,13,14,15,16},1,0)

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

Result:

here is your measure result compare with mine

Best Regards,

Lin

[Anonymous]

Hi Lin,

I get the same problem. Only 2 agents are shown in result for October when adding this filter;

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

It should be 18 agents who have made calls in October during working hours.

Only 2000 calls on 2 agents are shown, should show 5000 calls on 18 agents.

Where to troubleshoot?

Thanks in advance!

/Kris

[v-lili6-msft]

hi, @Anonymous

You may have a data structure or model error, Could you please share your demo pbix and some data sample and the expected output for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading

Best Regards,

Lin