DAX calculation max date and exclude data set

[aj1107]

Im trying to create a line chart using below scenario.

Table

issueid ,status ,substatus ,cdate

1 ,closed ,mpv ,01/15/2017

1 ,closed ,xyz ,02/19/2017

1 ,open ,xyz ,04/12/2017

2 ,closed ,xyz , 03/12/2017

2 ,closed ,xyz , 05/12/2017

3 ,closed ,xyz ,01/28/2017

Scenario :

condition1 Exlude the entire issue id group <if status="open" or status="mpv"> and Condition2 distinctcount(issueid) based on max(cdate) for the issue id. Highlighted green is eligible count when aggregated at month level.

Result : 

Month, Issue

Jan17, 1

May17, 1

Solution : I was able to get the result by implementing the sceanrio at database level  or through DAX query by creating calculated table (condition1) and calculated column rankx function to identify the max date for each issueid(condition2) and finally claculated measure to include condition1 and codition2 :

calculate(distinctcount(issueid),filter(table,issueid<>related(calculatedtable) && maxdate=1)) 

Just want to check is there any scope to finetune the dax calculation. Any other alternate way to get the same result.

Thank you,

[Phil_Seamark · Accepted solution]

Hi @aj1107

I had some success with this approach.  I created a new calcualted table using the following code

Table = 
VAR ExcludeThese = SUMMARIZE(FILTER('Table1','Table1'[status] ="Open" || Table1[substatus] = "mpv"),Table1[Issueid],"Blank",1)
VAR ReturnTable =SUMMARIZE(
            SUMMARIZE(
                FILTER(
                    NATURALLEFTOUTERJOIN(Table1,ExcludeThese),
                    [Blank] = blank()
                    ),
                    'Table1'[Issueid],"Max Date" ,
                    MAX('Table1'[cdate])
                    ),
                    [Max Date],
                    "Issue",
                    DISTINCTCOUNT(Table1[Issueid])
                    )
RETURN SELECTCOLUMNS(ReturnTable,"Month",FORMAT([Max Date],"MMMYY"),"Issue",[Issue])