Return value based on a condition

[Victormar]

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:

ID	DATE	MILEAGE	ISLATEST
1	1/1/2021	2300	0
1	3/1/2021	5000	1
2	7/1/2021	1000	0
2	10/1/2021	2000
2	11/1/2021	3000	0
2	12/1/2021	5500	1
3	3/1/2021	500
3	4/1/2021	1250	0
3	5/1/2021	7500	1
4	1/1/2021	200	0
4	3/1/2021	300
4	4/1/2021	500	1
5	2/1/2021	1000
5	7/1/2021	2500	0
5	12/1/2021	3000	1

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

[Victormar]

Thanks to both!!! @DataInsights & @Anonymous 

[Anonymous]

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.

[DataInsights · Accepted solution]

@Victormar,

Try this measure:

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