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

Passing the results of one measure through another

Hi all,

 

I 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 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 now, I want the second formula to pick up the Categories that correspond to ID's that come up from the first formula (at any given DateTime) and combine it into one measure. 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 that counts the number of Entries where In < DateTime < Out. 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 would like to see a bar chart showing all 3 'Entries':


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
Community Support Team
Community Support Team

Re: Passing the results of one measure through another

Hi @zudar

Is this what you want?

Capture4.JPG 

Add measure

have an entry =
CALCULATE (
    COUNT ( Entries[ID] ),
    FILTER (
        Entries,
        Entries[In] <= MAX ( 'calendar'[Date] )
            && (
                Entries[Out] >= MIN ( 'calendar'[Date] )
                    || Entries[Out] = BLANK ()
            )
    )
)

Modify measure

flag = IF([have an entry]<>BLANK(),IF([have a duration]=BLANK(),"Unknown",[return category]))

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Community Support Team
Community Support Team

Re: Passing the results of one measure through another

Hi @zudar 

I can create result as below.

They are all measures. (calculated columns don't change with the slicer)

have a duration =
CALCULATE (
    COUNT ( Entries[ID] ),
    FILTER (
        Durations,
        Durations[Start] <= MAX ( 'calendar'[Date] )
            && Durations[End] >= MIN ( 'calendar'[Date] )
            && Durations[Start] <> BLANK ()
    )
)

count of id = COUNT(Durations[ID])

return category =
VAR minstart =
    CALCULATE (
        MIN ( Durations[Start] ),
        FILTER ( ALLSELECTED ( Durations ), Durations[ID] = MAX ( Durations[ID] ) )
    )
RETURN
    LOOKUPVALUE (
        Durations[Category],
        Durations[ID], MAX ( Entries[ID] ),
        Durations[Start], minstart
    )


flag = IF([have a duration]=BLANK(),"Unknown",[return category])

Capture7.JPG

 

But I'm not clear which columns/measures  to add in a bar chart as you expected, could you show me more details?

 

Best Regards
Maggie
Community Support Team _ Maggie Li
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 of one measure through another

Hi @v-juanli-msft ,

 

That looks great, thank you! I tried to apply your solution to my (more complex) data model and it's not giving me what I was hoping for.. 😞

 

The 'Entries' and 'Durations' table have thousands of rows and I don't see how you filter the 'Entries' part based on the DateTime, I see you just filtered the 'Durations'. 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'. However, 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 (I think it's important to mention since you said that calculated columns don't change with the slicer):  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])

 

I would love to figure this out! Thank you for your help so far.

 

zudar Regular Visitor
Regular Visitor

Re: Passing the results of one measure through another

Just to check, I added a record to 'Durations' to see what your PBIX would show.

 

Capture.JPG

 

Indeed, ID = 321 should not be included in here..

Community Support Team
Community Support Team

Re: Passing the results of one measure through another

Hi @zudar

Is this what you want?

Capture4.JPG 

Add measure

have an entry =
CALCULATE (
    COUNT ( Entries[ID] ),
    FILTER (
        Entries,
        Entries[In] <= MAX ( 'calendar'[Date] )
            && (
                Entries[Out] >= MIN ( 'calendar'[Date] )
                    || Entries[Out] = BLANK ()
            )
    )
)

Modify measure

flag = IF([have an entry]<>BLANK(),IF([have a duration]=BLANK(),"Unknown",[return category]))

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

zudar Regular Visitor
Regular Visitor

Re: Passing the results of one measure through another

Hi Maggie ( @v-juanli-msft  ),

 

That's it, very nice! I realize that you now answered the question in my initial post correctly, but as I try to apply your solution to my actual data (model), the measures are not behaving as in (y)our example.

 

I suspect this has to do with the 'Orders' and 'Products' table that I initially didn't discuss, or it has to do with the fact that 'Entries' don't have a unique ID. So let me put all cards on the table and hope you're willing to give it a look, one last time. Here we go, we have:

 

'Entries' (added a row to show that ID is not unique in 'Entries' and corrected the 'Out' date for ID = 973),

ID

In

Out

973

13-10-2019 13:54

24-10-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 

288

10-10-2019 15:29

15-10-2019 13:28

 

'Durations' (with unique OrderID's),

OrderID

Start

End

64335436

15-10-2019 7:02

26-10-2019 16:38

64335437

12-10-2019 22:10

17-10-2019 12:20

64335438

13-10-2019 19:38

18-10-2019 17:25

64335439

13-10-2019 20:01

19-10-2019 13:55

 

'Orders' (with unique OrderID's),

OrderID

ID

Category

64335436

973

A

64335437

423

B

64335438

288

B

64335439

288

C

 

'Products' (with unique ID's),

ID

Name

973

Alex

423

Sam

288

John

229

Craig

 

'Durations' and 'Orders' have an 1-on-1 relationship.


The data model now looks like:

 

Capture2.PNG

 

Now let me show you how I changed the formulas you gave me:

 

 

 

have a duration = CALCULATE ( COUNT( Products[ID] ); FILTER ( Durations; Durations[Start] <= MAX ( 'calendar'[Date] ) && Durations[End] >= MIN ( 'calendar'[Date] ) && Durations[Start] <> BLANK () ) ) 

have an entry = CALCULATE ( COUNT ( Entries[ID] ); FILTER ( Entries; Entries[In] <= MAX ( 'calendar'[Date] ) && ( Entries[Out] >= MIN ( 'calendar'[Date] ) || Entries[Out] = BLANK () ) ) ) 

count of id = COUNT(Products[ID]) 

return category = VAR minstart = CALCULATE ( MIN ( Durations[Start] ); FILTER ( ALLSELECTED ( Orders ); Orders[ID] = MAX ( Orders[ID] ) ) ) RETURN LOOKUPVALUE ( Orders[Category]; Orders[ID]; MAX ( Entries[ID] ); Durations[Start]; minstart ) 

flag = IF([have an entry]<>BLANK();IF([have a duration]=BLANK();"Unknown";[return category]))

 

 

 

I'm trying to include the PBIX file in here, but I can't find how to do that, so I'll show you in a screenshot what the result is I get:

 

Capture3.PNG

 

Do you see what I'm doing wrong?

 

Also, I found out that the 'flag' field isn't allowed as input for the legend in the bar chart:

 

Capture4.PNG

 

Is it because it contains blank values? Or because it's a measure? Any ideas how to include the 'flag' as different colours/parts of that bar chart I have in the screenshot above?

 

Sorry about bothering you with all this stuff, but it's helping me a lot! Thanks for everything so far!

 

zudar Regular Visitor
Regular Visitor

Re: Passing the results of one measure through another

Hi Maggie ( @v-juanli-msft ),

 

I figured some stuff out and I'm almost there.. 

 

What my main mistake was in my last post was that had to use the 'ID' from table 'Orders' instead of 'Entries'.

 

But, then I found out that there's something wrong with the 'return category' measure..

 

Look at this:

 

Capture5.PNG

 

It's picking a category that belongs to a duration that doesn't include the selected date..

 

I changed the measure to this:

 

 

return category = 
VAR minstart =
    CALCULATE (
        MIN ( Durations[Start] );
        FILTER (
        Durations; [have a duration] = 1
    ))
RETURN
    LOOKUPVALUE (
        Orders[Category];
        Orders[ID]; MAX ( Entries[ID] );
        Durations[Start]; minstart
    )

 

 

And then it works..

 

But as I apply it to my actual model, I get this error:

 

Capture6.PNG

 

Do you have any ideas? 

zudar Regular Visitor
Regular Visitor

Re: Passing the results of one measure through another

I broke the measure up into two parts, 'minstart' and 'lookupvalue'. 'Minstart' is working fine:

 

minstart = CALCULATE (
        MIN ( Durations[Start] );
        FILTER (
        Durations;
        Durations[Start] <= MAX ( 'calendar'[Date] )
            && Durations[End] >= MIN ( 'calendar'[Date] )
            && Durations[Start] <> BLANK ()
    ))

 

It's the LOOKUPVALUE part that doesn't work for me. The uniqueness in 'Orders' and 'Durations' is in the 'OrderID'.

 

Shouldn't I then use 'OrderID' in the LOOKUPVALUE?

 

 

zudar Regular Visitor
Regular Visitor

Re: Passing the results of one measure through another

I added some data in the sample data to come to the same error as I have in my actual report:

 

Capture7.PNG

Now, when you drag in the 'return category' measure, it will give the same error. 

Highlighted
zudar Regular Visitor
Regular Visitor

Re: Passing the results of 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)