Passing the results of one measure through another

[zudar]

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

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 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!

[v-juanli-msft]

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])

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]

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]

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

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

[v-juanli-msft · Accepted solution]

Hi @zudar

Is this what you want?

 

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.

[zudar]

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:

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:

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:

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]

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:

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:

Do you have any ideas? 

[zudar]

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]

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

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

[zudar]

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