topic List matching values on set criteria in DAX Commands and Tips

List matching values on set criteria

JonoHill — Thu, 23 Jul 2020 01:05:16 GMT

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

Date	Work Area	Location	Employee	Time In	Time Out
21/07/2020	W1	L1	E1	5:00 AM	8:20 AM
21/07/2020	W1	L2	E1	8:20 AM	8:30 AM
21/07/2020	W1	L1	E2	5:00 AM	8:20 AM
21/07/2020	W1	L2	E2	8:20 AM	8:30 AM
21/07/2020	W1	L1	E3	5:00 AM	8:20 AM
21/07/2020	W1	L2	E3	8:20 AM	8:30 AM
21/07/2020	W1	L1	E4	5:00 AM	8:20 AM
21/07/2020	W1	L2	E4	8:20 AM	8:30 AM
21/07/2020	W1	L1	E5	5:00 AM	8:20 AM
21/07/2020	W1	L2	E5	8:20 AM	8: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.

Re: List matching values on set criteria

amitchandak — Thu, 23 Jul 2020 01:43:57 GMT

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))

Re: List matching values on set criteria

JonoHill — Thu, 23 Jul 2020 01:58:07 GMT

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.

Re: List matching values on set criteria

mahoneypat — Thu, 23 Jul 2020 12:38:19 GMT

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
)

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

Regards,

Pat