Forum Discussion

aj1107's avatar
aj1107
Icon for Advocate I rankAdvocate I
9 years ago
Solved

DAX calculation max date and exclude data set

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 ,...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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])
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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