Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
temptester
Frequent Visitor

Find rows before and after matching a condition

Hi,

 

I am very new to PBI and learning DAX functions. I need some help to do the following.

I have a table like following.

CodeCompletedDateProductDetail
11/01/2018A
11/04/2019C
123/05/2021B
29/02/2018C
26/05/2022D
34/01/2018K
35/05/2019A
36/03/2021E
31/09/2021C

 

I want to oder the table ASC of the completed date group by "Code". Then find all records before "ProductDetail" = C and after "ProductDetail" = C in to two tables group by "Code". For example, before C table would look like below.

CodeCompletedDateProductDetail
11/01/2018A
11/04/2019C
29/02/2018C
34/01/2018K
35/05/2019A
36/03/2021E
31/09/2021C

 

And after C table would like below.

CodeCompletedDateProductDetail
11/04/2019C
123/05/2021B
29/02/2018C
26/05/2022D
31/09/2021C


This is what I have tried so far.

 

After C =
VAR C_Rows = ADDCOLUMNS(Tbl,"C_CompletedDate",CALCULATE(MIN(Tbl[CompletedDate]),Tbl[ProductDetail] = "C")
)
RETURN
FILTER(C_Rows,Tbl[CompletedDate] >= [C_CompletedDate])



Thank you so much.

2 ACCEPTED SOLUTIONS
saud968
Super User
Super User

Step 1: Order the Table by CompletedDate
First, you need to order the table by CompletedDate within each Code group.

Step 2: Create Tables for Before and After "C"
You can use DAX to create two new tables: one for rows before "ProductDetail" = "C" and one for rows after "ProductDetail" = "C".

Here's how you can do it:

Table for Rows Before "C"
BeforeC =
FILTER(
Tbl,
Tbl[CompletedDate] < CALCULATE(MIN(Tbl[CompletedDate]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code]))
)
Table for Rows After "C"
AfterC =
FILTER(
Tbl,
Tbl[CompletedDate] >= CALCULATE(MIN(Tbl[CompletedDate]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code]))
)
Explanation
ALLEXCEPT(Tbl, Tbl[Code]): This function removes all filters from the table except for the Code column, ensuring that the calculation is done within each Code group.
CALCULATE(MIN(Tbl[CompletedDate]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code])): This calculates the minimum CompletedDate where ProductDetail is "C" within each Code group.
Example
Given your table, the BeforeC table will include all rows with CompletedDate before the first occurrence of "C" within each Code group, and the AfterC table will include all rows with CompletedDate from the first occurrence of "C" onwards.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

View solution in original post

We'll modify the DAX code to include ranking and then filter based on that rank.

Step 1: Add Ranking
First, let's create a calculated column to rank the rows within each Code group by CompletedDate.

Tbl =
ADDCOLUMNS(
Tbl,
"Rank", RANKX(
FILTER(Tbl, Tbl[Code] = EARLIER(Tbl[Code])),
Tbl[CompletedDate],
,
ASC
)
)
Step 2: Create Tables for Before and After "C"
Now, we can use this rank to filter the rows for the BeforeC and AfterC tables.

Table for Rows Before "C"
BeforeC =
FILTER(
Tbl,
Tbl[Rank] <= CALCULATE(MIN(Tbl[Rank]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code]))
)
Table for Rows After "C"
AfterC =
FILTER(
Tbl,
Tbl[Rank] >= CALCULATE(MIN(Tbl[Rank]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code]))
)
Explanation
ADDCOLUMNS: Adds a new column "Rank" to the table.
RANKX: Ranks the rows within each Code group by CompletedDate in ascending order.
EARLIER: Refers to the current row context within the RANKX function.
FILTER: Filters the rows based on the rank.
This should ensure that your tables are sorted by CompletedDate within each Code group and correctly split into before and after "C".

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

View solution in original post

3 REPLIES 3
temptester
Frequent Visitor

@saud968 Thank you so much. This works pretty well. Needed a slight alterteration for before C table "<=". However, currently the table is not sorted by CompletedDate within code groups. Can you please help me to do the first two steps. I know this has to be RANKX. But, can't figure out how to add RANKX to the FILTER Functions. Thanks again.

We'll modify the DAX code to include ranking and then filter based on that rank.

Step 1: Add Ranking
First, let's create a calculated column to rank the rows within each Code group by CompletedDate.

Tbl =
ADDCOLUMNS(
Tbl,
"Rank", RANKX(
FILTER(Tbl, Tbl[Code] = EARLIER(Tbl[Code])),
Tbl[CompletedDate],
,
ASC
)
)
Step 2: Create Tables for Before and After "C"
Now, we can use this rank to filter the rows for the BeforeC and AfterC tables.

Table for Rows Before "C"
BeforeC =
FILTER(
Tbl,
Tbl[Rank] <= CALCULATE(MIN(Tbl[Rank]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code]))
)
Table for Rows After "C"
AfterC =
FILTER(
Tbl,
Tbl[Rank] >= CALCULATE(MIN(Tbl[Rank]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code]))
)
Explanation
ADDCOLUMNS: Adds a new column "Rank" to the table.
RANKX: Ranks the rows within each Code group by CompletedDate in ascending order.
EARLIER: Refers to the current row context within the RANKX function.
FILTER: Filters the rows based on the rank.
This should ensure that your tables are sorted by CompletedDate within each Code group and correctly split into before and after "C".

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

saud968
Super User
Super User

Step 1: Order the Table by CompletedDate
First, you need to order the table by CompletedDate within each Code group.

Step 2: Create Tables for Before and After "C"
You can use DAX to create two new tables: one for rows before "ProductDetail" = "C" and one for rows after "ProductDetail" = "C".

Here's how you can do it:

Table for Rows Before "C"
BeforeC =
FILTER(
Tbl,
Tbl[CompletedDate] < CALCULATE(MIN(Tbl[CompletedDate]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code]))
)
Table for Rows After "C"
AfterC =
FILTER(
Tbl,
Tbl[CompletedDate] >= CALCULATE(MIN(Tbl[CompletedDate]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code]))
)
Explanation
ALLEXCEPT(Tbl, Tbl[Code]): This function removes all filters from the table except for the Code column, ensuring that the calculation is done within each Code group.
CALCULATE(MIN(Tbl[CompletedDate]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code])): This calculates the minimum CompletedDate where ProductDetail is "C" within each Code group.
Example
Given your table, the BeforeC table will include all rows with CompletedDate before the first occurrence of "C" within each Code group, and the AfterC table will include all rows with CompletedDate from the first occurrence of "C" onwards.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.