Struggling with looking up a value

[zudar]

Hi all,

I have the following report:

Entries, Durations, Orders and Products are all tables that I use in this report. You can also find them at the bottom of this post. In addition, I have a dax-generated calendar.

What I'd like to achieve is a stacked bar chart where I can mark all 'products' that have valid 'entries' with the 'best' corresponding category from 'orders'. By 'best corresponding category', I mean the category that belongs to a valid duration, and if there are multiple valid durations, pick the one that has the earliest start date. In this case it would look somewhat like the bar chart in the screenshot above but then: 3 ID's:

229 -> Coloured with Category 'Unknown'

288 -> Coloured with Category 'B'

973 -> Coloured with Category 'A'

'Final result' is where I get stuck for now. Let me break it down for you:

For every Products[ID] and selected Calendar[Date], I check if..

..they have a Duration using:

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

..they have an Entry using:

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

..what the minimum Start-date/time is for all 'valid' Durations using:

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

Now I want a last column that will return the corresponding Orders[Category] for the given Products[ID] and 'minstart'. I tried using:

return category = 
VAR minstart =
    CALCULATE (
        MIN ( Durations[Start] );
        FILTER (
        Durations;
        Durations[Start] <= MAX ( 'calendar'[Date] )
            && Durations[End] >= MIN ( 'calendar'[Date] )
            && Durations[Start] <> BLANK ()
    ))
RETURN
    LOOKUPVALUE(
        Orders[Category];
        Orders[ID]; MAX (Products[ID]);
        Durations[Start]; minstart
    )

But I get:

Any idea what's going wrong? I'm struggling to understand what's happening within the LOOKUPVALUE part of the 'return category' measure.

Your help is very much appreciated. If someone cares to explain how I can upload a pbix-file in this post, I will be happy to do that.

-----------------------------------------------------------------------

Additional information:

Orders:

OrderID	ID	Category
64335430	229
64335431	288
64335432	229	D
64335436	973	A
64335437	423	B
64335438	288	B
64335439	288	C

Durations:

OrderID	Start	End
64335430
64335431
64335432
64335436	15-10-2019 07: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

Entries:

ID	In	Out
973	13-10-2019 13:54	24-10-2019 08:12
288	15-10-2019 15:29	18-10-2019 13:28
229	16-10-2019 13:25	20-10-2019 09:28
321	10-10-2019 14:35	13-10-2019 19:08
288	10-10-2019 15:29	15-10-2019 13:28

Products:

ID	Name
973	Alex
423	Sam
288	John
229	Craig

[v-chuncz-msft]

@zudar 

You may try TOPN to return the [OrderID].

• https://community.powerbi.com/t5/Desktop/Card-Help/td-p/514770
• https://community.powerbi.com/t5/Desktop/Return-another-column-based-on-condition/td-p/773314

[zudar]

@v-chuncz-msftHi, I'm trying to apply it but I don't see how because I'm working from multiple tables.. I have the ID from Products, a '1' for every ID that includes the sliced datetime based on the entries, a '1' for every ID that includes the sliced datetime based on the durations, the minimum start date that belongs to the duration(s) that includes the sliced datetime... and now I just need to pick up the corresponding Category that belongs to a certain ID and minimum start date. 

Measurex = 
MAXX(
    TOPN( 1; FILTER (
        Durations;
        Durations[Start] <= MAX ( 'calendar'[Date] )
            && Durations[End] >= MIN ( 'calendar'[Date] )
            && Durations[Start] <> BLANK ()
    ); ...; ... );
...
)   

I feel like I'm overcomplicating.. 

[zudar · Accepted solution]

created a spin-off topic: 

https://community.powerbi.com/t5/Desktop/How-to-lookup-a-value-based-on-conditions/m-p/862364#M41377...