Forum Discussion

passants's avatar
passants
Frequent Visitor
4 years ago
Solved

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 IDYear
A2018
A2019
A2020
B2019
C2018
C2019

 

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 IDYearPresent in next year?
A2018TRUE
A2019TRUE
A2020FALSE
B2019FALSE
C2018TRUE
C2019FALSE

 

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

  • 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())