Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Last Date with Filter DAX

Hello There,   I am wondering if I should be using power BI to calculate my last date based on certain condition.   For ex:   I have a below table:   Batch Name| Process Name| Status | last s...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

You should be able to create a measure like:

 

Measure = CALCULATE(MAX('Table'[Date]),FILTER('Table',[Status]="Success"))

Put this in a table along with your columns you specified and you should be good to go.

 

 

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago
    @smoupre wrote:

    You should be able to create a measure like:

     

    Measure = CALCULATE(MAX('Table'[Date]),FILTER('Table',[Status]="Success"))

    Put this in a table along with your columns you specified and you should be good to go.

     

     

    Greg_Deckler

    Just in case, you may use the process date('Table'[Date]) in a slicer,

     

    Measure =
CALCULATE (
    MAX ( 'Table'[Date] ),
    ALLEXCEPT ( 'Table', 'Table'[Batch Name], 'Table'[Process Name] ),
    FILTER ( 'Table', [Status] = "Success" )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Thanks for the quick response Greg_Deckler & Eric_Zhang.

     

    Yes, the DAX looks correct but when I use it in my report, it filters the entire table and shows only successful processes. 

     

    So Basically, it doesn't show failed process and last successful date against it.

     

     

     

    So in the above test report, I need a last date against the first record, however, rest of the records should be visible too. 

     

    When I use above DAX,  it filters all the record for status = "Completed".

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Just to give you an overall view,

       

      It should look something like below:

       

      Batch Name | Process Date| Process Name  | status | last date

      B1 | 12-05-2017|P1 | PN1| Completed |  (NULL)

      B2 | 13-05-2017|P2 | PN2| Completed |  (NULL)

      B1 | 14-05-2017|P1 | PN1| Failed| 12-05-2017

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        9 years ago

        Anonymous

        Try

        Measure = 
VAR LastSuccessDate =CALCULATE (
    MAX ( 'Table'[Process Date] ),
    ALLEXCEPT ( 'Table', 'Table'[Batch Name], 'Table'[Process Name] ),
    FILTER ( 'Table', [Status] = "Completed" )
)
RETURN IF(ISBLANK(LastSuccessDate),"",LastSuccessDate)