Filter for Common years in DAX

[mgrayTCB]

I cant get my head around how to do this... I want to filter the table for only the years that the projects have in common. I wont know how many projects are in the table or what the common years are. I just want the common years so I can add the rent values together and calculate annual yoy rent growth for properties that existed in those years.

Table
Property	rent	year
Project 1	50	2020
Project 1	100	2021
Project 1	120	2022
Project 1	110	2023
Project 1	105	2024
Project 1	125	2025
Project 1	100	2026
project 2	600	2021
project 2	550	2022
project 2	580	2023
project 2	570	2024
project 2	490	2025
project 3	800	2021
project 3	825	2022
project 3	875	2023

I would want this result

Expected Result
Property	rent	year
project 3	800	2021
project 3	825	2022
project 3	875	2023
project 2	600	2021
project 2	550	2022
project 2	580	2023
Project 1	100	2021
Project 1	120	2022
Project 1	110	2023

[v-pgoloju]

Hi @mgrayTCB,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Also, a special thanks to @Deku and @Sahir_Maharaj  for the quick and helpful response.

Just following up to check if the solution shared by our Super User @Deku helped resolve your issue. If you're still facing difficulties or need further assistance, please let us know — we’re here to help!
If the response addressed your query, we kindly request you to mark it as Accepted Solution and click Yes if you found it helpful. This supports others in the community as well.

Best regards,
Prasanna Kumar

[Sahir_Maharaj]

Hello @mgrayTCB,

Can you please try this approach:

CommonYears =
VAR AllProjects = DISTINCT('Table'[Property])
VAR CommonYears =
    FILTER (
        VALUES('Table'[year]),
        CALCULATE (
            COUNTROWS (
                FILTER (
                    AllProjects,
                    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Property] = EARLIER('Table'[Property]) && 'Table'[year] = EARLIER('Table'[year]) ) > 0
                )
            ) = COUNTROWS(AllProjects)
        )
    )
RETURN
    FILTER (
        'Table',
        'Table'[year] IN CommonYears
    )

[mgrayTCB]

Not sure why this approach is not working....

[Deku]

Measure =

Var tbl= Addcolumn(

Values(table[property]),

"@minYear", calculate( min( table[year] )),

"@maxYear", calculate( max( table[year] ))

)

Var minYear = MAXX( tbl, [@minYear])

Var maxYear = MINX( tbl, [@maxYear])

Var years = 

Filter(

Values( table[year] ),

Table[year] >=minYear &&

Table[year] <= maxYear 

)

Return

If( selectedvalue( table[year] ) in years, 1)

Add the is measure to filter pane of the visual and filter to 1

[mgrayTCB]

I am not following what this is supposed to do but it does not seem to work

[Deku]

Rows 6 and 7 are wrong. You want the max min year and the min max year.

[mgrayTCB]

What else am I missing... That did not change anything

[Deku · Accepted solution]

Sorry forgot to removefilters

Measure =

Var tbl=

Calculatetable(

Addcolumn(

Values(table[property]),

"@minYear", calculate( min( table[year] )),

"@maxYear", calculate( max( table[year] ))

),

Allselected()

)

Var minYear = MAXX( tbl, [@minYear])

Var maxYear = MINX( tbl, [@maxYear])

Var years =

Filter(

All( table[year] ),

Table[year] >=minYear &&

Table[year] <= maxYear

)

Return

If( selectedvalue( table[year] ) in years, 1)

[mgrayTCB]

That worked. Although I still cant get my head around the logic.