how to display data that match all date selected only - AND operator

[lulu45]

I have data with employee id and date that the employee worked:

EmpId     DateWorked

A             8/14/17 ; 8/15/17 ; 8/16/17 ; 8/17/17 ; 8/18/17 

B              8/15/17 ; 8//16/17; 8/18/17

C              8/14/17 ; 8/15/17  ; 8/16/17 ; 8/17/17; 8/18/17  

D              8/14/17 ; 8/15/17; 8/16/17 ; 8/17/17 ; 8/18/17

E               8/17/17 ; 8/18/17

How do i display only empid that worked 8/14/17 and 8/15/17 and 8/16/17 and 8/17/17 and 8/18/17 : emp A and D only.

I am very new to power BI, any help is appreciated.

Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

The answer should be A,C and D.  I think i have solved the problem.  See the screenshots below

[CheenuSing · Accepted solution]

Hi @lulu45

I have done the solution for you. Please check the uploaded pbix file.

https://1drv.ms/u/s!ApP3mBZyGaHfzytuXMwJntCSEq2H

Go through the measures in Calendar Table and EmpWorked Tables.  Go through edit query to understand the unpivoting of the emp worked table.

If this works for you please accept this as solution and also give KUDOS.

Cheers

CheenuSing

[lulu45]

Hi ChenuSing,

Thank you again, it worked perfectly for my data.

I am just curious if i had duplicate date, how do i modify the WorkedDays ?  to also pick up that date?

Best Regards,

lulu45

[CheenuSing]

Hi @lulu45

To take care of duplicate dates in the fact table use the following

WorkedDays = CALCULATE(DISTINCTCOUNT('EmpWorked'[Date]),FILTER (ALLSELECTED('Calendar'),'Calendar'[WeekDay] < 6 && 'Calendar'[Holiday] = 0 ), DATESBETWEEN(EmpWorked[Date],(MIN('Calendar'[Date])),(MAX('Calendar'[Date])) ) )

By using distinctcount, even if there are duplicate dates for an employee id it will be treated as ONE and not many.

Cheers

CheenuSing

[lulu45]

Hi CheenuSing,

When you have time, could you check for me, when i select my slicer from 8/14/17 to 9/7/17 : 18days,

In my query to get date if  i select date from 8/14/17 thru 9/7/2017 i had a staff worked

8/14/17

8/18/17

8/21/17

8/22/17

8/23/17

8/24/17

8/25/17

8/26/17

8/28/17

8/29/17

8/30/17

8/31/17

9/1/17

9/2/17

9/3/17

9/5/17

9/6/17

9/7/17

 and this staff is on the report ( this staff not worked on 8/15/17 ; 8/16/17; 8/17/17 but worked on 8/26/17: 9/2/17; 9/3/17) .

I checked the calendar and 8/26/17 had weekday=6 and 9/2/17 had weekday=6; 9/3/1017 had weekday=7.

I copy from page 6 of your and add in my data so the formula should be correct for my.  

Right now i could get by by spell out each date that i want in the query not use the range date.

Best Regards,

Lulu45

[lulu45]

Thank you for all your help CheenuSing!

Lulu45

[Ashish_Mathur]

Hi @CheenuSing,

Your solution yields the incorrect result.  When i select 17/8/2017 to 18/8/2017 in your slicer, the result is A,C,D,E.  The result should be only E becuase E was the only one who worked on both those days.  Atleast that is what i think the @lulu45 wants.

[lulu45]

I want to display all that worked all the day that the slicer selected ( and operator) , so if you select 8/17/17 and 8/18/17, then A/C/D/E should be display. I am sorry for not explain what i want correctly.

[CheenuSing]

Hi @Ashish_Mathur,

The business rule says

"I want to display from timesheet ONLY all employees that worked continously from 8/14/17 to 9/7/17 ( not count saturday and sunday and not count 9/4/17) so it will be 18 days continously.  When i put in a slicer of date if i select only day from 8/14/17 to 9/7/17 it will also display those who did not worked all 18 days."

As per this any body who wroked continuously in the selected period should be reported. Hence my result.  A,C, and D also worked continuously on those two days.

If the business rule were to say employees who worked contiuously ONLY in the period selected then only E should be reported. Probably AC and D may need to be eliminated because they had worked even prior.

This clarification should be provided by @lulu45 .

Cheers

CheenuSing

[Ashish_Mathur]

Hi @CheenuSing,

You are correct.  @lulu45 should clarify the result expected is the dates are 17/8/2017 and 18/8/2017.

[lulu45]

Thank you so very much CheenuSing for did the example for me to check.

I will learn it.

Thanks you again !

[Ashish_Mathur · Accepted solution]

Hi,

The answer should be A,C and D.  I think i have solved the problem.  See the screenshots below

[lulu45]

Thank you so much for your help.

I dont know how to do it but if you can show me how then i could learn.

Thanks again!

[Ashish_Mathur]

Hi @lulu45,

You may refer to my solution here.

Hope this helps.

[GilbertQ]

Hi @lulu45

There is more than one way to show this data.

What you could do is to put the EmpID as a Slicer and when you selected each EmpID it would then show you the dates that they worked?

So my question is what are you trying to display with the data below?

[lulu45]

Hi Guavaq,

Thank you so much for helping me.

I want to display from timesheet ONLY all employees that worked continously from 8/14/17 to 9/7/17 ( not count saturday and sunday and not count 9/4/17) so it will be 18 days continously.  When i put in a slicer of date if i select only day from 8/14/17 to 9/7/17 it will also display those who did not worked all 18 days.

I only want to display A C and D in the example that i post and i will apply to my data.

Thank you!