Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I opened multiple topics over the last few weeks, but unfortunately I only came really close to my desired solution. I feel like I'm so close and I need someone to give me that last push. If I figure this out, it will 'make' my month. Here we go..
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. The data model looks like:
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' (no corresponding duration available, thus no category)
288 -> Coloured with Category 'B' (multiple durations available, the one with earliest start-date has category 'B')
973 -> Coloured with Category 'A' (one corresponding duration, 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 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 ()
)
)
)
..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 ()
)
)
..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', possibly using the OrderID as identifier, not sure what's the wisest thing to do.
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 |
321 | David |
Hi @zudar ,
Do you want to create a calculated column which return [category]?
But which table do you want to create the column?
According to your description, the column has two conditions: [ID] & [minstart].
Is your [minstart] a measure?
Is the [category] valid if the category belongs to a valid duration?
Is it like this?
If vaild category =
VAR x=
CALCULATE (
COUNT( Products[ID]),
FILTER (
Duration,
Duration[Start] <= MAX ( 'calendar'[Date] )
&& Duration[End] >= MIN ( 'calendar'[Date] )
&& Duration[Start] <> BLANK ()
)
)
RETURN
IF(
x > 0,
TRUE(),
FALSE()
)
Category =
IF(
[minstart] <> BLANK(),
LOOKUPVALUE(
'Order'[Category],
'Order'[ID], Products[ID],
'Order'[If vaild category], TRUE()
),
BLANK()
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |