topic Filter for Common years in DAX in DAX Commands and Tips

Filter for Common years in DAX

mgrayTCB — Mon, 14 Apr 2025 18:56:16 GMT

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

Re: Filter for Common years in DAX

Deku — Mon, 14 Apr 2025 19:09:08 GMT

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

Re: Filter for Common years in DAX

Sahir_Maharaj — Mon, 14 Apr 2025 19:18:08 GMT

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 )

Re: Filter for Common years in DAX

mgrayTCB — Mon, 14 Apr 2025 20:21:38 GMT

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

Re: Filter for Common years in DAX

Deku — Mon, 14 Apr 2025 20:27:41 GMT

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

Re: Filter for Common years in DAX

mgrayTCB — Mon, 14 Apr 2025 20:57:05 GMT

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

Re: Filter for Common years in DAX

Deku — Mon, 14 Apr 2025 21:03:19 GMT

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)

Re: Filter for Common years in DAX

mgrayTCB — Mon, 14 Apr 2025 21:19:07 GMT

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

Re: Filter for Common years in DAX

mgrayTCB — Mon, 14 Apr 2025 21:19:58 GMT

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

Re: Filter for Common years in DAX

Anonymous — Tue, 15 Apr 2025 04:16:39 GMT

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