Passing the results from one measure through another

[zudar]

Would really appreciate some advice how to solve this issue I'm dealing with. I'm in the following situation. I've got three relevant tables.

'Entries':

ID	In	Out
973	13-10-2019 13:54	24-03-2019 8:12
288	15-10-2019 15:29	18-10-2019 13:28
229	16-10-2019 13:25	20-10-2019 9:28

'Durations':

ID	Start	End	Category
973	15-10-2019 7:02	26-10-2019 16:38	A
423	12-10-2019 22:10	17-10-2019 12:20	B
288	13-10-2019 19:38	18-10-2019 17:25	B
288	13-10-2019 20:01	19-10-2019 13:55	C

At last, '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 of those ID's were between 'Start' and 'End' at that time.. but only including ID's from the second group that were part of the first result set! In addition, I want to mark each 'Entry' with the Category from 'Durations'. If it doesn't find a corresponding 'Duration', I want to mark the 'Entry' with Category 'unknown'. If it finds multiple corresponding 'Durations', I want to pick the one with the earliest 'Start' date/time. Creating both measures separately worked out pretty well. 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 count-calculation. (Don't mind the extra filters I applied, I had to take some Blank fields into account.) So, I want the second formula to only pick up the Category from Durations and apply it to the correct Entry in the first formula, preserving all Entries in the final result set (even when they don't have a corresponding Duration). This sounded easy to me at first, but I've been struggling for days to find an appropriate method for solving this.

In the end, I want to create a stacked bar chart with the DateTime on the x-axis that counts the number of Entries. 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 'unknown'. With the sample data above and the slicer set at (for example) 17-10-2019 10:00, I want to see:

Entry ID = 973 has one corresponding Duration, with Category A.

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

Entry ID = 229 has no corresponding Durations at all, so Category is 'unknown'.

So a stacked bar chart with 3 parts, one marked with a color corresponding to 'Category A', another one with 'Category B' and the last marked with Category 'unknown'. In the end, I hope to make this chart look at an entire week of Entries and Durations and show the results per hour.

I hope this all makes sense; let me know if it doesn't. Thank you!

[dax]

Hi zudar,

If possible could you please inform me more detailed information(such as your sample data and your expected output)? Then I wil help you more correctly. By the way, could you please explain "In the end, I hope to make this chart look at an entire week of Entries and Durations and show the results per hour." to me?

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zudar]

Hi @dax,

I posted sample data with expected output in my initial post.

Let me give you some extra information as well:

The 'Entries' and 'Durations' table have thousands of rows. Maybe my sample data was misleading, but the example I gave where the slicer is set on a DateTime that includes all 'Entries', was just 'lucky'. I still want to show all 'Entries' that fullfill the In < DateTime < Out condition, and apply the category from all 'Durations' that fulfill the Start < DateTime < End condition.

In addition, I've left out the following in my initial post:  The 'Entries' table is just as I described, but the 'Durations' is a little different.

The 'Entries' has a nice simple setup: ID -> In -> Out

The 'Durations': OrderID -> Start -> End

Then I have a table called 'Orders': OrderID -> ID -> Category 

Durations and Orders have a 1-on-1 relationship. I assumed this wouldn't be an issue and I created calculated columns in 'Durations' that said: ID = RELATED(Orders[ID]) & Category = RELATED(Orders[Category]). But obviously, I would rather also use the 'Orders' tables without those calculated columns.

Let me know if this helps. Thank you for your help so far.

[zudar · Accepted solution]

I created a spin-off topic here: https://community.powerbi.com/t5/Desktop/Struggling-with-looking-up-a-value/m-p/860829#M413002