Forum Discussion
Helper IVFilter for Common years in DAX
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 |
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)
9 Replies
- Deku
Super User
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
- 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 )
mgrayTCBNot sure why this approach is not working....
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
