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

Struggling with looking up a value

Hi all,

 

I have the following report:

 

Capture7.PNG

 

 

 

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.

 

Capture2.PNG

 

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:

Capture8.PNG

 

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:

OrderIDIDCategory
64335430229 
64335431288 
64335432229D
64335436973A
64335437423B
64335438288B
64335439288C

 

Durations:

OrderIDStartEnd
64335430  
64335431  
64335432  
6433543615-10-2019 07:0226-10-2019 16:38
6433543712-10-2019 22:1017-10-2019 12:20
6433543813-10-2019 19:3818-10-2019 17:25
6433543913-10-2019 20:0119-10-2019 13:55

 

Entries:

IDInOut
97313-10-2019 13:5424-10-2019 08:12
28815-10-2019 15:2918-10-2019 13:28
22916-10-2019 13:2520-10-2019 09:28
32110-10-2019 14:3513-10-2019 19:08
28810-10-2019 15:2915-10-2019 13:28

 

Products:

IDName
973Alex
423Sam
288John
229Craig
1 ACCEPTED SOLUTION

Accepted Solutions
zudar Regular Visitor
Regular Visitor

Re: Struggling with looking up a value

3 REPLIES 3
Community Support Team
Community Support Team

Re: Struggling with looking up a value

@zudar 

 

You may try TOPN to return the [OrderID].

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
zudar Regular Visitor
Regular Visitor

Re: Struggling with looking up a value

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

Re: Struggling with looking up a value

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)