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 w...
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])
        )
    )
)
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?