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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Sams11
Frequent Visitor

Filter on status and date condition

Hi all,

please help me with this following questions:
I am new to powe bi and trying this...

 

I have column with status and updated date. I am not sure how to achieve this, kindly provide your input. Thanks in advance.

Ex:

Status = Closed AND (status changed to Closed after '2023/04/01' AND status changed to Closed before '2023/04/30')

1 ACCEPTED SOLUTION

Hi @Sams11 

1.You can insert new step after named(Custom1)

vxinruzhumsft_1-1700535859031.png

2.and insert the follwing code

=Table.SelectRows(the last step name,e.g #"Changed Type", each ([issuetype] ="Defect") and ([status]="Closed"))

3.Then insert a new step again, named Custom2

=Table.SelectRows(Custom1, each [date] >#date(2023, 4, 1) and [date] < #date(2023, 4, 30))

Best Regards!

Yolo Zhu

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

 

 

View solution in original post

6 REPLIES 6
watkinnc
Super User
Super User

Of course if you'd like to FILTER with these conditions, add a new step, and in the formula bar, enter:

 

= Table.SelectRows(PriorStepOrTableName, each Status] = "Closed" and [updated date] > #date(2023, 4, 1) and [updated date] < #date(2023, 4, 30))

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

This is my exact jira query which I am tryingto achive in power BI..

 

issuetype = Defect AND status = Closed AND (status changed to Closed after '2023/04/01' AND status changed to Closed before '2023/04/30')

 

Hi @Sams11 

1.You can insert new step after named(Custom1)

vxinruzhumsft_1-1700535859031.png

2.and insert the follwing code

=Table.SelectRows(the last step name,e.g #"Changed Type", each ([issuetype] ="Defect") and ([status]="Closed"))

3.Then insert a new step again, named Custom2

=Table.SelectRows(Custom1, each [date] >#date(2023, 4, 1) and [date] < #date(2023, 4, 30))

Best Regards!

Yolo Zhu

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

 

 

But I have one problem here.. Since I am using DirectQuery-> I do not have access to table view and custom columns...

I can only do right click "Add Column", Is there a way I can acheive this?

m_dekorte
Super User
Super User

On the ribbon select: Add Column/Custom Column

A dialog box opens, enter this code into the custom column formula area:

 

[Status] = "Closed" and [updated date] > #date(2023, 4, 1) and [updated date] < #date(2023, 4, 30)

 

Names between [ ] can also be selected from and entered by double clicking their name in the Available columns section on the right hand side of the dialog box. Please note that power query's M is case sensitive.

 

I hope this is helpful

But I have one problem here.. Since I am using DirectQuery-> I do not have access to table view and custom columns...

I can only do right click "Add Column", Is there a way I can acheive this?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors
Top Kudoed Authors