Forum Discussion
Calculated column which can evaluate other rows in the table with the same value?
Hello,
I am working on an issue where I think I understand the logic behind what needs to happen, but I am not sure of the DAX to make it work.
Here is some example data:
| Person ID | Year |
| A | 2018 |
| A | 2019 |
| A | 2020 |
| B | 2019 |
| C | 2018 |
| C | 2019 |
I would like a calculated column that returns a TRUE when there is another row in the table with the same Person ID with that row's year+1
So, essentially, in our example it would look like this:
Table1:
| Person ID | Year | Present in next year? |
| A | 2018 | TRUE |
| A | 2019 | TRUE |
| A | 2020 | FALSE |
| B | 2019 | FALSE |
| C | 2018 | TRUE |
| C | 2019 | FALSE |
My first thought was to try and use the =CONTAINS function like so, where for the first row, it would use =FILTER to create a table of all of the rows where [Person ID]=A, then evaluate the column [Year] for it containing that row's [Year]+1 (in this example, 2018+1)
Column = contains(
filter('Table1','Table1'[Person Id]=[Person Id]),
'Table1'[Year],
('Table1'[Year]+1)
)
But, this doesn't work, and I am not sure if this is even the best way to go about doing this. I am hoping for other suggestions.
Thank for any help!
passants ,
new column
= var _cnt = countx(filter(Table, [Person ID] = earlier([Person ID] ) && [Year] = earlier([Year] ) +1) , [Person ID])
return
if(isblank(_cnt), false() , true())
1 Reply
- amitchandak
Super User
passants ,
new column
= var _cnt = countx(filter(Table, [Person ID] = earlier([Person ID] ) && [Year] = earlier([Year] ) +1) , [Person ID])
return
if(isblank(_cnt), false() , true())