Forum Discussion

Sams11's avatar
Sams11
Frequent Visitor
2 years ago
Solved

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')

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Sams11 

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

    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.

     

     

6 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    2 years ago

    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

    • Sams11's avatar
      Sams11
      Frequent Visitor
      2 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

    • Sams11's avatar
      Sams11
      Frequent Visitor
      2 years ago

      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?

    • Sams11's avatar
      Sams11
      Frequent Visitor
      2 years ago

      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')

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Sams11 

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

        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.