Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JonoHill
Frequent Visitor

List matching values on set criteria

Hi. I need to write a measure that will generate a list of unique names that match a set criteria. An anonymized data sample is below of what I have to work with. 

 

What I want to do is generate a unique list the other people that were in the same location and the selected (via slicer) person at the same time as the selected person. Would be good if this would look back on the previous 14 days from a selected date also, but not essential. 

 

Sample Data

DateWork AreaLocationEmployeeTime InTime Out
21/07/2020W1L1E15:00 AM8:20 AM
21/07/2020W1L2E18:20 AM8:30 AM
21/07/2020W1L1E25:00 AM8:20 AM
21/07/2020W1L2E28:20 AM8:30 AM
21/07/2020W1L1E35:00 AM8:20 AM
21/07/2020W1L2E38:20 AM8:30 AM
21/07/2020W1L1E45:00 AM8:20 AM
21/07/2020W1L2E48:20 AM8:30 AM
21/07/2020W1L1E55:00 AM8:20 AM
21/07/2020W1L2E58:20 AM8:30 AM

 

If you need more clarity on what I want to achieve, let me know and I will add any details you need.

Thanks for looking.

1 ACCEPTED SOLUTION

Here is one way to do this.  I put your date in a table called Locations.  Also, I modified some of the in/out times as you had the same values for all employees in same location.

 

1.  Make a separate table with just the names of Employees for your slicer with Employees = DISTINCT(Locations[Employee])

2. Put the Employee[Employee] column in a slicer

3. Make a table visual with your Locations[Employee] column and also add this measure

 

Overlapped =
VAR mintime =
CALCULATE (
MIN ( Locations[Time In] ),
TREATAS ( VALUES ( Employees[Employee] ), Locations[Employee] )
)
VAR maxtime =
CALCULATE (
MIN ( Locations[Time Out] ),
TREATAS ( VALUES ( Employees[Employee] ), Locations[Employee] )
)
VAR location =
CALCULATE (
MIN ( Locations[Location] ),
TREATAS ( VALUES ( Employees[Employee] ), Locations[Employee] )
)
VAR overlap =
CALCULATE (
COUNTROWS (
FILTER (
Locations,
Locations[Time In] <= maxtime
&& Locations[Time Out] >= mintime
)
),
Locations[Location] = location
)
RETURN
IF (
AND (
HASONEVALUE ( Locations[Date] ),
SELECTEDVALUE ( Employees[Employee] ) <> SELECTEDVALUE ( Locations[Employee] )
),
overlap
)

 

overlap.png

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@JonoHill , in a visual; use a measure like this with location

measure =
var _loc = summarize(filter(Table, Table[employee] in selectedvalue(Table[employee])),Table[Location])
return
calculate(coutrows(Table), filter(all(Table), Table[Location] in _loc && Table[Date]>=today()-14))

VAR __SelectedEmployees =
    VALUES ( ContactData[Employee] )
VAR __loc =
    SUMMARIZE (
        FILTER ( ContactData, ContactData[Employee] IN __SelectedEmployees ),
        ContactData[Location]
    )
RETURN
    CALCULATE (
        COUNTROWS ( ContactData ),
        FILTER (
            ALL ( ContactData ),
            ContactData[Location] IN __loc
                && ContactData[Date]
                    >= TODAY () - 14
        )
    )

I have used the above and it seems to work on my Data model. (I had to modify the use of SelectedValue since the front end of this is actually sitting in Excel currently).

 

However, this is returning a number of people where what I actually need is a list of the names of all the people who were in the same location as the selected employee. It also needs to take into account the Time In and Time Out fields (which are actually DateTime) to determine if they were there at the same time. 

 

Thanks for your time, the first attempt was closer than I managed to get after hours of trying. 

Here is one way to do this.  I put your date in a table called Locations.  Also, I modified some of the in/out times as you had the same values for all employees in same location.

 

1.  Make a separate table with just the names of Employees for your slicer with Employees = DISTINCT(Locations[Employee])

2. Put the Employee[Employee] column in a slicer

3. Make a table visual with your Locations[Employee] column and also add this measure

 

Overlapped =
VAR mintime =
CALCULATE (
MIN ( Locations[Time In] ),
TREATAS ( VALUES ( Employees[Employee] ), Locations[Employee] )
)
VAR maxtime =
CALCULATE (
MIN ( Locations[Time Out] ),
TREATAS ( VALUES ( Employees[Employee] ), Locations[Employee] )
)
VAR location =
CALCULATE (
MIN ( Locations[Location] ),
TREATAS ( VALUES ( Employees[Employee] ), Locations[Employee] )
)
VAR overlap =
CALCULATE (
COUNTROWS (
FILTER (
Locations,
Locations[Time In] <= maxtime
&& Locations[Time Out] >= mintime
)
),
Locations[Location] = location
)
RETURN
IF (
AND (
HASONEVALUE ( Locations[Date] ),
SELECTEDVALUE ( Employees[Employee] ) <> SELECTEDVALUE ( Locations[Employee] )
),
overlap
)

 

overlap.png

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors