Enforcing a result from one measure through another measure

[zudar]

Hi all,

Would really appreciate some advice and guidance how to solve this issue I'm dealing with.

I'm in the following situation (very simplified). I've got three relevant tables:

1) Table 'Entries':

ID	In	Out
9732590	20-03-2019 13:54:23	24-03-2019 8:12:45
...	...	...

2) Table 'Durations':

ID	Start	End
9732590	15-03-2019 7:02:36	26-03-2019 16:38:09
6435233	18-03-2019 14:22:44	27-03-2019 10:21:43
...	...	...
...	...	...

3) At last, table 'DateTime', which is just a basic calendar coming from DAX formula I found online.

Now I have to create a dashboard that shows how many ID's where between 'In' or 'Out' at some variable DateTime.. and how many ID's were between 'Start' and 'End' at that time.. but only including ID's in the second group that were part of the first! 

Creating both measures separately worked out pretty well so far. I have:

Number of ID's In = CALCULATE(COUNTROWS(Entries);

FILTER(Entries;

Entries[In]<=MAX(DateTime[DateTime])

&& (Entries[Out]>=MIN(DateTime[DateTime]) || Entries[Out] = BLANK())))

Number of ID's Started = CALCULATE(DISTINCTCOUNT(Durations[ID]);

FILTER(Durations;

Durations[Start]<=MAX(DateTime[DateTime])

&& (Durations[End]>=MIN(Kalender[DateTime])

&& (Durations[Start]<>BLANK()))

Entries never overlap and Durations possibly do, that's why there's a difference in the calculation. (Don't mind the extra filters I applied, I had to take some Blank fields into account.)

Now I want the second formula to only show the ID's that come up from the first formula (at any given DateTime).

This sounded easy to me at first, but I've been struggling for hours to find an appropriate method for solving this.

Very simplified, I want to see two measures in my report:

Slicer with DateTime is set at, for example, 22-03-2019 14:27:11

Number of ID's In = 1

And the second measure showing:

Number of ID's Started = 1

Note that I used the data sample I gave earlier and ID = 6435233 was excluded from the second measure (because it isn't part of the result set of the first measure).

I tried working it out as an extra filter in the second formula and also using a bridge table, but I keep getting stuck.

Help is very much appreciated. 🙂 

[zudar]

@Anonymous You're absolutely right, I now see how my question can be confusing. Maybe I oversimplified. See the thing is, that the Durations table contains an ID, Start and End times, but also classifies each Durations in (let's say) a 'Category'. Now the difficulty that I left out of the initial question, is that I basically want to apply those same categories to the Entries.

So Durations looks like:

ID	Start	End	Category
423	12-10-2019 22:10	17-10-2019 12:20	A
288	13-10-2019 19:38	18-10-2019 17:25	A
288	13-10-2019 20:01	19-10-2019 13:55	B
...	...	...	...

And Entries looks like:

ID	In	Out
288	15-10-2019 15:29	18-10-2019 13:28
693	14-10-2019 17:01	19-10-2019 11:14
229	16-10-2019 13:25	20-10-2019 9:28
...	...	...

What I want to create is the following (let's say with the slicer on DateTime set at 17-10-2019 10:00):

A stacked bar chart that counts the number of Entries where In < DateTime < Out. In this case, all the above Entries are included, 3 in total. Then, I want to use the Durations to add a Category to the Entries in the bar chart, giving a color for each Category and if it doesn't find one in the Durations set it to an 'unknown' category. Also, again the requirement Start < DateTime < End also holds for the Durations. As you can see above, Entries never overlap for the same ID, but this can happen in Durations, also with different Categories.

With the sample data above and the slicer set at (for example) 17-10-2019 10:00, I would like to see a bar chart showing:

3 Entries:

Entry with ID = 288 has two possible Durations and I want to pick the one with the earliest start date, this gives Category A.

Entry with ID = 693 has no corresponding Durations, so Category is 'unknown'.

Entry with ID = 229 has no corresponding Durations, so Category is again 'unknown'

So a stacked bar chart with 3 parts (3 Entries), 1 marked with a color corresponding to 'Category A' and the other two marked with Category 'unknown'. In the end, I hope to make this stacked bar chart look at an entire week of Entries and Durations and show the results for each hour.

I hope this helped.

Anyway, thanks a lot for your help so far. Appreciate it.

[zudar]

Any ideas?

[Anonymous]

@zudar  - I would recommend that you start a new thread, and try to make the question more straight-forward. Refer to this blog for tips.

[Anonymous]

@zudar  - Try the following Measure. It finds the list of IDs from each and then counts the values in the intersection.

In Both = 
var _Entries = FILTER(
    Entries,
    Entries[In]<=MAX(DateTime[DateTime]) && 
    (Entries[Out]>=MIN(DateTime[DateTime]) || Entries[Out] = BLANK())
)
var _Durations = FILTER(
    Durations,
    Durations[Start]<=MAX(DateTime[DateTime])
    && Durations[End]>=MIN(DateTime[DateTime])
    && Durations[Start]<>BLANK()
)
var _In = SELECTCOLUMNS(_Entries, "ID", Entries[ID])
var _DistinctStarted = SUMMARIZE(_Durations,[ID])
return COUNTROWS(
    INTERSECT(_In,_DistinctStarted)
)

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[zudar]

Hi @Anonymous!

Thanks, that helped a lot. Only thing is that I wasn't really looking for the intersection between both result sets, but more like a 'left join', so preserving all ID's that come out of the 'Entries' part of the measure and find the corresponding 'Durations'.

Any idea on how to do that? I tried to substitute the 'INTERSECT' with 'NATURALLEFTOUTERJOIN' but that didn't seem to work with the variables.. 

[Anonymous]

@zudar  - The intersect assumes that you consider both time ranges, but I think you're saying that you want to find the ID's based on the date/time in Entries and do not filter based on date/time in Durations. I'm not sure exactly what you're looking for - I think the following are the possibilities:

1. Count the number of Durations per ID meeting the Entries requirements.
2. Count the number of Durations per ID meeting the Entries requirements AND meet the Durations requirements. (The measure I sent does this)
3. Display ID, Start, End of Durations for each ID meeting the Entries requirements. (It sounds like this is what you want.)
4. Display ID, Start, End of Durations for each ID meeting the Entries requirements AND meet the Durations requirements.

[zudar]

@AnonymousYou're absolutely right, I now see how my question can be confusing. Maybe I oversimplified. See the thing is, that the Durations table contains an ID, Start and End times, but also classifies each Durations in (let's say) a 'Category'. Now the difficulty that I left out of the initial question, is that I basically want to apply those same categories to the Entries.

So Durations looks like:

ID	Start	End	Category
423	12-10-2019 22:10	17-10-2019 12:20	A
288	13-10-2019 19:38	18-10-2019 17:25	A
288	13-10-2019 20:01	19-10-2019 13:55	B
...	...	...	...

And Entries looks like:

ID	In	Out
288	15-10-2019 15:29	18-10-2019 13:28
693	14-10-2019 17:01	19-10-2019 11:14
229	16-10-2019 13:25	20-10-2019 9:28
...	...	...

What I want to create is the following (let's say with the slicer on DateTime set at 17-10-2019 10:00):

A stacked bar chart that counts the number of Entries where In < DateTime < Out. In this case, all the above Entries are included, 3 in total. Then, I want to use the Durations to add a Category to the Entries in the bar chart, giving a color for each Category and if it doesn't find one in the Durations set it to an 'unknown' category. Also, again the requirement Start < DateTime < End also holds for the Durations. As you can see above, Entries never overlap for the same ID, but this can happen in Durations, also with different Categories.

With the sample data above and the slicer set at 17-10-2019 10:00, I would like to see a bar chart showing:

• 3 Entries:
  • Entry with ID = 288 has two possible Durations and I want to pick the one with the earliest start date, this gives Category A.
  • Entry with ID = 693 has no corresponding Durations, so Category is 'unknown'.
  • Entry with ID = 229 has no corresponding Durations, so Category is again 'unknown'
• So a stacked bar chart with 3 parts (3 Entries), 1 marked with a color corresponding to 'Category A' and the other two marked with Category 'unknown'.

In the end, I hope to make this stacked bar chart look at an entire week of Entries and Durations and show the results for each hour. 

I hope this helped. Anyway, thanks a lot for your help so far. Appreciate it.