Forum Discussion
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.
| Code | CompletedDate | ProductDetail |
| 1 | 1/01/2018 | A |
| 1 | 1/04/2019 | C |
| 1 | 23/05/2021 | B |
| 2 | 9/02/2018 | C |
| 2 | 6/05/2022 | D |
| 3 | 4/01/2018 | K |
| 3 | 5/05/2019 | A |
| 3 | 6/03/2021 | E |
| 3 | 1/09/2021 | C |
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.
| Code | CompletedDate | ProductDetail |
| 1 | 1/01/2018 | A |
| 1 | 1/04/2019 | C |
| 2 | 9/02/2018 | C |
| 3 | 4/01/2018 | K |
| 3 | 5/05/2019 | A |
| 3 | 6/03/2021 | E |
| 3 | 1/09/2021 | C |
And after C table would like below.
| Code | CompletedDate | ProductDetail |
| 1 | 1/04/2019 | C |
| 1 | 23/05/2021 | B |
| 2 | 9/02/2018 | C |
| 2 | 6/05/2022 | D |
| 3 | 1/09/2021 | C |
This is what I have tried so far.
Thank you so much.
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!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!
3 Replies
- saud968
Memorable Member
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! 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.
- saud968
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!
