How do measure conflicting values for only specific conflicts?

[Julian1]

I have a table with a set of times and activities, like the one shown below. There can be multiple rows with a given time for overlapping activities, such as the first two rows below that show both Soccer and Football are being played at 10/1/2020 5:00 PM:

DateTime	Activity
10/1/2020 5:00 PM	Soccer
10/1/2020 5:00 PM	Football
10/1/2020 6:00 PM	Tennis
10/1/2020 7:00 PM	Soccer
10/1/2020 7:00 PM	Tennis

I'd like to create a measure with a count of all the time slots that have conflicting activities, but only for specific conflicts. As an example, theres a conflict at 10/1/2020 5:00 PM since both Soccer and Football would use the same playing field. However, there's not a conflict at 10/1/2020 7:00 PM even though there are 2 activities at the same time, since Soccer and Tennis use different playing fields. 

I tried to use GroupBy for this but couldn't figure out how to interrogate CurrentGroup() to find out if the specific conflicts I needed to detect were there. Thoughts welcome. 🙂

[v-shex-msft]

Hi @Julian1,

I think you need to add a calculated column or active type table to group current activity types then you can simply use date and type to check the conflicting activities. 

Regards,

Xiaoxin Sheng

[amitchandak]

@Julian1 , I will two-step approach

I will create first this column

This can var in another column

New Activity =
Switch( True(),
[Activity] in {"Soccer", "Football"} , "SoccerFootball",
[Activity]
)

And this conflict column.

conflict = countx(filter(table, [DateTime] =earlier([DateTime]) && [Activity] =earlier([Activity])),[Activity])

measure

conflict = countx(filter(allselected(table), [DateTime] =max([DateTime]) && [Activity] =max([Activity])),[Activity])

conflict > 1 is what you are looking for