Forum Discussion

Penn's avatar
Penn
Resolver I
7 years ago
Solved

DAX Question: Return the first date based on another column

Hi Everyone,

 

My first post here, very new to Power BI. Let's imaging a table [Work Status] that looks like below.

 

 

Now I want to create a new column to return the first date of Finished status for each unique Item ID which is 7/11/2013.

 

The DAX which I wrote doesn't work:

Status Finished Date = CALCULATE(MIN('Work Status'[Date From], FILTER('Work Status', 'Work Status'[Status] = "Finished"))

 

Proprobaly I should not use CALCULATE?

 

Need help thanks!

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Penn 

     

    Sorry I think I misplaced the brackets

     

    Try this one

     

    Status Finished Date =
CALCULATE (
    MIN ( 'Work Status'[Date From] ),
    FILTER (
        ALLEXCEPT ( 'Work Status', 'Work Status'[item id] ),
        'Work Status'[Status] = "Finished"
    )
)

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Penn 

     

    Try with this revision

     

    Status Finished Date =
CALCULATE (
    MIN (
        'Work Status'[Date From],
        FILTER (
            ALLEXCEPT ( 'Work Status', 'Work Status'[item id] ),
            'Work Status'[Status] = "Finished"
        )
    )
)

     

    • Penn's avatar
      Penn
      Resolver I
      7 years ago

      Hi Zubair,

       

      I tried your code in both measure and calculated column and it returns me the "single value cannot be determined" error.

       

      How should I fix this?

       

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

        Penn 

         

        Sorry I think I misplaced the brackets

         

        Try this one

         

        Status Finished Date =
CALCULATE (
    MIN ( 'Work Status'[Date From] ),
    FILTER (
        ALLEXCEPT ( 'Work Status', 'Work Status'[item id] ),
        'Work Status'[Status] = "Finished"
    )
)