Forum Discussion
Return a value based on a condition
Hi community,
So I have a table with columns refering to measurements we are registering, and also the same amount of columns refering the measure with IsLatest, no show which is the last measurement we got, something like:
| ID | date | Mileage | IsLatest |
| 1 | 1-1-21 | 100 | 0 |
| 1 | 2-1-21 | 200 | |
| 1 | 7-1-21 | 450 | 1 |
| 2 | 3-1-21 | 125 | |
| 2 | 4-1-21 | 250 | 1 |
| 3 | 1-1-21 | 75 | 0 |
| 3 | 4-1-21 | 250 | 1 |
I am creating a table where I put different columns and one is refered to this Mileage, and what we are doing to get the latest value is simply by adding a filter to the visualization where IsLatest=1. This colum has a 1 to identify the latest measurement, and can have 0 or blank when it's not.
The problem is that then I want to be able to filter a line chart, to see the evolution over time of the Mileage, but of course if I try to filter clicking on the id of the table, it will only show the latest value.
Is there any way to create a new column or measurement to return only the values when IsLatest is 1, so I don't have to add the filter to the visualization and therefore I can see al the evolution of the mileage on the linechart?
Thanks in advance 🙂
Hi Victormar ,
From my understanding, you want to create a IsLatest column.
You could create a measure like
IsLatest = VAR _MAX = MAXX ( FILTER ( ALL ( 'Table' ), [VALUE] <> BLANK () && [ID] = MAX ( 'Table'[ID] ) ), [DATE] ) RETURN IF ( _MAX = MAX ( 'Table'[DATE] ), 1, IF ( SUM ( 'Table'[VALUE] ) <> BLANK (), 0 ) )Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Victormar ,
From my understanding, you want to create a IsLatest column.
You could create a measure like
IsLatest = VAR _MAX = MAXX ( FILTER ( ALL ( 'Table' ), [VALUE] <> BLANK () && [ID] = MAX ( 'Table'[ID] ) ), [DATE] ) RETURN IF ( _MAX = MAX ( 'Table'[DATE] ), 1, IF ( SUM ( 'Table'[VALUE] ) <> BLANK (), 0 ) )Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
What are you doing to create this custom table that you have shown?
You can simple pick the latest values in this table and then plot the line chart based on that.
If you don't need the IsLatest flag explicitly, you can drop it and only have a table with latest values.
If you can provide more information about the data, I cna show how this can be done
Hi,
Thanks for answering. The table is in a sql server, I am just importing it to power bi. The thing is that it is updated every day, so the value 1 for IsLatest column is changing everyday, only if there is a reading from the value.
The table I have put was just a simplified copy to show what I have
I mean that while we have a timestamp column, somethimes the table would have a new row without data for the same ID, so IsLatest would still be in an earlier date.
I need the IsLatest to show in a table the last reading we have, but then I cannot use the table to filter the line chart, because there I want to have all readings.
ID DATE ISLATEST VALUE 1 1-1-21 1 2-1-21 0 1000 1 4-1-21 1 1500 1 8-1-21 2 2-1-21 0 100
2 4-1-21 0 200 2 8-1-21 1 450 3 2-1-21 So we would have '1' for the latest reading, but when there is a new reading, the column 'IsLatest' would change from 0 to 1, while adding another value to the Mileage column. We can also have a new row with IsLatest 'blank', which will result in no value for the measurement column.
Hope it makes sense.
