DAX Count based on Criteria outside the range of filter

[ramiaburayeh]

Dear all,

I want to count distinict  all cases for each day gourp by the last action per day( Open, Under Review, Close) and add the cases that has not been closed ( Action <> Close) from prevous date. The following is the dataset: 

Case_ID	Action	Created_Date
A	Open	20/6/2022 12:00:00
A	Under Review	20/6/2022 12:15:00
B	Open	21/6/2022 10:00:00
B	Under Review	22/6/2022 11:07:00
A	Close	22/6/2022 12:00:00
C	Open	22/6/2022 11:07:00
C	Under Review	23/6/2022 11:07:00
D	Open	23/6/2022 16:07:01
D	Under Review	23/6/2022 16:07:01

The following table illustrates the expected results (Total) column

Date	Action	Total Cases	Open from past dates	Total
20/6/2022	Under Review	1	0	1
21/6/2022	Open	1	0	1
21/6/2022	Under Review	0	1	1
22/6/2022	Under Review	1	0	1
22/6/2022	Close	1	0	1
22/6/2022	Open	1	1	2
23/6/2022	Under Review	2	1	3
23/6/2022	Open	0	1	1

Thank you in advance

[ramiaburayeh]

Thank you very much

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Total Cases: = 
VAR _currentdate =
    MAX ( 'Calendar'[Date] )
VAR _currentactionindex =
    MAX ( 'Action'[Index] )
VAR _newtable =
    SUMMARIZE (
        FILTER ( ALL ( Data ), Data[Created_Date] = _currentdate ),
        'Case'[Case_ID],
        'Action'[Index]
    )
VAR _addmaxactionindex =
    SUMMARIZE (
        ADDCOLUMNS (
            _newtable,
            "@maxactionindex",
                MAXX (
                    FILTER ( _newtable, 'Case'[Case_ID] = EARLIER ( 'Case'[Case_ID] ) ),
                    'Action'[Index]
                )
        ),
        'Case'[Case_ID],
        [@maxactionindex]
    )
RETURN
    COUNTROWS (
        FILTER ( _addmaxactionindex, [@maxactionindex] = _currentactionindex )
    )

Open from past: = 
VAR _currentdate =
    MAX ( 'Calendar'[Date] )
VAR _currentactionindex =
    MAX ( 'Action'[Index] )
VAR _todayclosecase =
    SUMMARIZE (
        FILTER (
            ALL ( Data ),
            Data[Created_Date] = _currentdate
                && Data[Action] = "Close"
        ),
        'Case'[Case_ID]
    )
VAR beforecurrenttable =
    SUMMARIZE (
        FILTER (
            ALL ( Data ),
            Data[Created_Date] < _currentdate
                && Data[Case_ID] <> _todayclosecase
        ),
        'Case'[Case_ID],
        'Action'[Index]
    )
VAR _addmaxactionindex =
    FILTER (
        SUMMARIZE (
            ADDCOLUMNS (
                beforecurrenttable,
                "@maxactionindex",
                    MAXX (
                        FILTER ( beforecurrenttable, 'Case'[Case_ID] = EARLIER ( 'Case'[Case_ID] ) ),
                        'Action'[Index]
                    )
            ),
            'Case'[Case_ID],
            [@maxactionindex]
        ),
        [@maxactionindex] <> 3
    )
RETURN
    COUNTROWS (
        FILTER ( _addmaxactionindex, [@maxactionindex] = _currentactionindex )
    )

Total measure: = 
[Total Cases:] + [Open from past:]