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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Julian1
Employee
Employee

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

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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