cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

## Filter all the lines that based on status

Hello everyone.

I got stucked on this and need a bit of assistance.

So I have this table, and its connected using the OrderLine#

 Order Number OrderLine# Sales AORDER 1A 1000 AORDER 2B 1000 AORDER 1C 1000 BORDER 1CC 2000

 OrderLine# Status Timestamp 1A Inquiry 11/5/22 6:00 AM 1A Booked 11/6/22 6:00 AM 1A Shipped 11/7/22 6:00 AM 1A Closed 11/8/22 6:00 AM 2B Inquiry 11/9/22 6:00 AM 2B Booked 11/10/22 6:00 AM 2B Cancelled 11/12/22 6:00 AM 1C Inquiry 11/13/22 6:00 AM 1C Booked 11/14/22 6:00 AM 1C Shipped 11/15/22 6:00 AM

The goal here is to only show the result of a status = closed

in this case

If I filter the Order Number = AORDER

It will only show this,

 OrderLine# DateDiff 1A 3

*Date Diff is the date difference between - 11/5/22 and 11/8/22 (from Inquiry to closed)

Again the goal is to show only the orderline# that is closed but need to consider all the historical of it.

1A - in our example this show because it has a closed status

2B - this didnt show because it doesnt have a closed status

1C -  this didnt show because it doesnt have a closed status

1 ACCEPTED SOLUTION
Community Support

Create a calculated column.

``````datediff =
VAR _Closed =
MAXX (
FILTER (
'Table (2)',
[OrderLine#] = EARLIER ( [OrderLine#] )
&& [Status] = "Closed"
),
[Timestamp]
)
VAR _Inquiry =
MAXX (
FILTER (
'Table (2)',
[OrderLine#] = EARLIER ( [OrderLine#] )
&& [Status] = "Inquiry"
),
[Timestamp]
)
RETURN
IF (
_Closed <> BLANK ()
&& _Inquiry <> BLANK (),
DATEDIFF ( _Inquiry, _Closed, DAY )
)``````

Put it to the filter, and select "is not blank", then you will get the final output.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

Create a calculated column.

``````datediff =
VAR _Closed =
MAXX (
FILTER (
'Table (2)',
[OrderLine#] = EARLIER ( [OrderLine#] )
&& [Status] = "Closed"
),
[Timestamp]
)
VAR _Inquiry =
MAXX (
FILTER (
'Table (2)',
[OrderLine#] = EARLIER ( [OrderLine#] )
&& [Status] = "Inquiry"
),
[Timestamp]
)
RETURN
IF (
_Closed <> BLANK ()
&& _Inquiry <> BLANK (),
DATEDIFF ( _Inquiry, _Closed, DAY )
)``````

Put it to the filter, and select "is not blank", then you will get the final output.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Resolver I

Thanks, it's working!

The EARLIER is what I'm missing 😯.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors