Forum Discussion

Julian1's avatar
Julian1
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

How do measure conflicting values for only specific conflicts?

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:

DateTimeActivity
10/1/2020 5:00 PMSoccer
10/1/2020 5:00 PMFootball
10/1/2020 6:00 PMTennis
10/1/2020 7:00 PMSoccer
10/1/2020 7:00 PMTennis

 

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. 🙂

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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