Forum Discussion

Victormar's avatar
Victormar
Helper V
4 years ago
Solved

Return value based on a condition

Hi all,

 

I have a table with different measurements we are doing, and we mark the latest measurement adding it a 1 in another column (IsLatest). Then I am building a table where I want to show only the latest values, so the filter would be IsLatest=1, have tried different expressions but can't make it work. 

The table would have many measurements related to the same ID, but only the last one would be identified with a 1 in the IsLatest column, the other rows would be o or blank. 

Something like:

IDDATEMILEAGEISLATEST
11/1/202123000
13/1/202150001
27/1/202110000
210/1/20212000 
211/1/202130000
212/1/202155001
33/1/2021500 
34/1/202112500
35/1/202175001
41/1/20212000
43/1/2021300 
44/1/20215001
52/1/20211000 
57/1/202125000
512/1/202130001

 

What I need to do is create a new table that will have some other columns with measurements, and just show the latest measurement from this one. I can make it work adding the filter IsLatest=1 to the table/visualization, but I would like to have it on the measure itself. 

 

Thanks for helping 🙂 

Have a nice day

Need Help

3 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    Victormar,

     

    Try this measure:

     

    Latest Mileage =
CALCULATE ( SUM ( Table1[MILEAGE] ), Table1[ISLATEST] = 1 )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Victormar ,

    Please create a measure first.

    Measure =
VAR max_date =
    CALCULATE (
        MAX ( 'Table'[DATE] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] ) )
    )
RETURN
    IF ( MAX ( 'Table'[DATE] ) = max_date, 1, BLANK () )

     

    Then create a table.

    Table 2 = FILTER('Table',[Measure]=1)

     

    If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

     

    Best Regards

    Community Support Team _ Polly

     

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