cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zudar Regular Visitor
Regular Visitor

Passing the results from one measure through another

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':

IDInOut
97313-10-2019 13:5424-03-2019 8:12
28815-10-2019 15:2918-10-2019 13:28
22916-10-2019 13:2520-10-2019 9:28 


'Durations':

IDStartEndCategory
97315-10-2019 7:0226-10-2019 16:38A
423 12-10-2019 22:1017-10-2019 12:20B
288 13-10-2019 19:3818-10-2019 17:25B
288 13-10-2019 20:0119-10-2019 13:55C

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
zudar Regular Visitor
Regular Visitor

Re: Passing the results from one measure through another

3 REPLIES 3
Community Support Team
Community Support Team

Re: Passing the results from one measure through another

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 Regular Visitor
Regular Visitor

Re: Passing the results from one measure through another

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.

Highlighted
zudar Regular Visitor
Regular Visitor

Re: Passing the results from one measure through another

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)