Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
OK, I tried all different kinds of solutions to this and finally decided to just nuke it from orbit. Just to be sure... So basically, the task was that given a set of overlapping meetings, find the total amount of time spent in meetings. So, the basic process is to blow out the meetings from the start to the end for every minute of the day and then check if each minute is actually included in a meeting or not. That's the gist of it.
Overlap =
VAR __Start = MIN('Table'[Start])
VAR __End = MAX('Table'[End])
RETURN
IF(ISBLANK(MAX([Start])),BLANK(),
VAR __Table = GENERATESERIES(__Start,__End,1/24/60)
VAR __Table1 = ALL('Table')
VAR __Table2 = GENERATE(__Table,__Table1)
VAR __Table3 = ADDCOLUMNS(__Table2,"Include",IF([Value]>=[Start] && [Value] <= [End],1,0))
VAR __Table4 = GROUPBY(__Table3,[Value],"Minute",MAXX(CURRENTGROUP(),[Include]))
RETURN
SUMX(__Table4,[Minute])/60
)
I also threw in a "Not Included" version that kind of does the opposite:
Not Included =
VAR __Start = MIN('Table'[Start])
VAR __End = MAX('Table'[End])
RETURN
IF(ISBLANK(MAX([Start])),BLANK(),
VAR __Table = GENERATESERIES(__Start,__End,1/24/60)
VAR __Table1 = ALL('Table')
VAR __Table2 = GENERATE(__Table,__Table1)
VAR __Table3 = ADDCOLUMNS(__Table2,"Include",IF(COUNTROWS(FILTER(__Table1,[Value]>=[Start] && [Value] <= [End]))>=1,0,1))
VAR __Table4 = GROUPBY(__Table3,[Value],"Minute",MAXX(CURRENTGROUP(),[Include]))
RETURN
SUMX(__Table4,[Minute])/60
)
eyJrIjoiZDVjMzVhYmEtMzdlZS00ZmRkLWEyMzktNWRlZTc4ZGMyMzdhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
@Greg_Deckler Wow, this for sure a variation of a problem I am trying to solve.
I have some observation and questions based on this, that may help me solve my problem.
Here are my Observations
- I see that when I select a particular meeting, the Overlap Card visual give me either 1.00 or 0.50 which is good. And by clicking each indivual meeting I was able to get the following result for each index
1 - 1.00
2 - 0.05
3 - 0.05
4 - 0.05
5 - 1.00
6 - 1.00
7 - 1.00
8 - 1.00
- I also noticed that when no meeting is selected, the Overlap Card returns 3.00
Here are my Questions
1. How do I get the Overlap Card to return the Sum of the Overlap result (5.15) when their's no filter?
2. How do I get the result to sum up correctectly when a few meetings a selected?
Thanks in advance.
@Euto Ah, that is a measures total problem. The goal for Overlap was to get the total amount of time actuall spent in meetings that had overlapping times. So, the total amount of time overall is 3 hours. If you want the table to total to the individual values in the rows, you need the following:
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907