Forum Discussion

rkgundabhat's avatar
rkgundabhat
Helper I
2 years ago
Solved

Filter data

Hello , 

My table has these columns CaseNumber ,Department, CaseOpenedDate, CaseTerminatedDate, CaseCloseDate. I want to know cases which were open in a given month. My example query for the cases which were open on Sep 2022 would be

Select * from Table where CaseOpenedDate <= '30-Sep-2022' and CaseTerminatedDate is null or CaseTerminatedDate >= '01-sep-2022' and CaseCloseDate is null or CaseCloseDate >= '01-sep-2022' 

In my dashboard I want to be able to select Date filter and Department and get all the cases where were open in any month. 

Need help with this please. 

 

Thank you

RK

 

Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    rkgundabhat 

    Please try this measure:

    Open Cases = 
CALCULATE(
    COUNTROWS('CaseTable'),
    FILTER(
        'CaseTable',
        'CaseTable'[CaseOpenedDate] <= MAX('Dates'[Date]) &&
        (
            'CaseTable'[CaseTerminatedDate] = BLANK() ||
            'CaseTable'[CaseTerminatedDate] >= MIN('Dates'[Date])
        ) &&
        (
            'CaseTable'[CaseCloseDate] = BLANK() ||
            'CaseTable'[CaseCloseDate] >= MIN('Dates'[Date])
        )
    )
)

9 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    rkgundabhat 

    Please try this measure:

    Open Cases = 
CALCULATE(
    COUNTROWS('CaseTable'),
    FILTER(
        'CaseTable',
        'CaseTable'[CaseOpenedDate] <= MAX('Dates'[Date]) &&
        (
            'CaseTable'[CaseTerminatedDate] = BLANK() ||
            'CaseTable'[CaseTerminatedDate] >= MIN('Dates'[Date])
        ) &&
        (
            'CaseTable'[CaseCloseDate] = BLANK() ||
            'CaseTable'[CaseCloseDate] >= MIN('Dates'[Date])
        )
    )
)
    • rkgundabhat's avatar
      rkgundabhat
      Helper I
      2 years ago

      I think from the above DAX code , I only have CaseTable. What is the Dates table and the Date column?

    • rkgundabhat's avatar
      rkgundabhat
      Helper I
      2 years ago

      This is working as I wanted. So I created a meansure as you suggested and created dates table from below.  I added this OpenCases measure to my Cases table and used the Dates table as filter. 

      Thank you so much Fowmy

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.

    • rkgundabhat's avatar
      rkgundabhat
      Helper I
      2 years ago
      CaseNumberDepartmentCaseOpenedDateCaseTerminatedDateCaseCloseDate
      1231HR6/2/20216/2/20226/3/2022
      1232Accounts9/15/20226/2/202310/2/2023
      1233Facility2/23/2023 6/2/2023
      1234IT2/1/201910/2/202211/2/2022

      The request is "Show all the cases active in a given month for a department" . 

      I figured that I need to use the above query to get my the result which would be cases 1232 and 1234. 

      In my report canvas I have filter for department so if I select IT then my count should be 1. 

      I am not able to understand what filter do I need to select a year/month in the canvas to get the count for a  department and how to use the query.