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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MonaliC
Frequent Visitor

Return a value based on condition and filters

Hello,

 

This is a sample data, I want to populate two columns here Open Date and Close Date with following condition

= if status is open, then return date in "Open Date" column but for each unique A_ID and B_ID, please see the sample below.

so basically for each unique A_ID and B_ID there are two statuses I want to populate date basis OPen or Close condition considering A_ID and B_ID 

MonaliC_0-1728555529904.png

Any help would be highly appreciated. Thank you.

1 ACCEPTED SOLUTION
shafiz_p
Memorable Member
Memorable Member

Hi @MonaliC  Try below code:

For openDate:

shafiz_p_0-1728557700954.png

 

 

For Close Date:

shafiz_p_1-1728557753032.png

 

 

But for ID 103, there is 4 rows with only one close. So all 4 rows will have that close date and for open date first will be used.

Hope this helps!!

If this solved your problem, Please accept it as a solution!!

 

Best Regards,
Shahariar Hafiz

View solution in original post

6 REPLIES 6
shafiz_p
Memorable Member
Memorable Member

Hi @MonaliC  Try below code:

For openDate:

shafiz_p_0-1728557700954.png

 

 

For Close Date:

shafiz_p_1-1728557753032.png

 

 

But for ID 103, there is 4 rows with only one close. So all 4 rows will have that close date and for open date first will be used.

Hope this helps!!

If this solved your problem, Please accept it as a solution!!

 

Best Regards,
Shahariar Hafiz

@shafiz_p  Thanks much. This works. I am accepting it as solution. 
Also for 103 as you see there are 4 of them, so the idea is that we need to consider pair- Open and Close so for first pair open close will be the earliest open and earliest close and for second pair second earliest open and second earliest close and so on. How can I get that result? could you please help.

@MonaliC  You need PairID to correctly Identify each pair.  If you have single pair then then mark it 1 and if you have multiple pair, then mark it 1, 2, 3 etc.

First sorting the A_ID in ascending order in power query.

Then create calculated column "PairID". See image below:

shafiz_p_0-1728658577683.png

 

Then update the open date and close date code. See images below:

shafiz_p_2-1728658732186.png

shafiz_p_3-1728658760970.png

 

 

Hope this helps!!

If this solved your problem, accept it as a solution and a kudos!!

 

 

Best Regards,
Shahariar Hafiz

@shafiz_p 
All my IDs are alphanumeric, Pair_ID results isn't the same as yours, please see below:
This is an example same A_ID-103 above, but below example isn't giving correct pair as per status. Could you please help here. Appreciate it a lot. Thank you.

MonaliC_0-1729496454395.png

 

Hello @MonaliC  Check out the below image, it is perfectly working for me. 

shafiz_p_0-1729500133468.png

 

Check your formula. Also, make sure that your ID column is unique. If problem still exist, try creating an Index column using power query and try to use that column as ID.

Hope this solve your problem!!

 

Best Regards,
Shahariar Hafiz

It worked now. Thanks much. It was probably my typo in the formula.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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