Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Get the First value and Last value Only

I have a table with a production line information
like
Model
Start Date
End Date
and others

I want to show in a Column Visual only the first data when the model was registered and the last date that was registered
Similar to this 

Each color represents a Car Model 
I dont need to display the middle values , just the first and last one

It is possible?

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Have you looked at MIN/MAX, FIRSTDATE/LASTDATE, FIRSTNONBLANK/LASTNONBLANK ?

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Visual Control = 
var mindate = 
CALCULATE(
    MIN('Table'[Date]),
    FILTER(
        ALLEXCEPT('Table','Table'[Model]),
        [IsReistered]="Yes"
    )
)
var maxdate = 
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        ALLEXCEPT('Table','Table'[Model]),
        [IsReistered]="Yes"
    )
)
return
IF(
    MAX('Table'[IsReistered])="Yes"&&(MAX('Table'[Date])=mindate||MAX('Table'[Date])=maxdate),
    1,0
)

     

    Then you need to put the measure in the visual level filter to filter the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      It works, but I only get the last date 

      In your visual yo have the first date when the model A entered and the last dat whne the model change occurrs 

      and I only get the latest date of each model

      what could be possible wrong?

       

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        5 years ago

        Hi, Anonymous 

         

        Could you please show us some sample data and expected results with OneDrive for business? Do mask sensitive data before uploading. Thanks.

         

        Best Regards

        Allan