Create one filter for two columns with Year

[mic_rys]

Hi,

I have problem how to create "global year filter"

table:

Proj name	implemented	deleted
Project 1	2010
Project 2	2011	2014
Project 3	2012	2015
Project 4	2013
Project 5	2014

And i have 2 measures which show number of implemented and deleted projects:

Measure1 :

year	Numer of implemented
2010	1
2011	1
2012	1
2013	1
2014	1

Measure2

year	Numer of deleted
2014	1
2015	1

I would like to create filter Year. For example if i choose 2014 measure should show:

year	Numer of implemented
2014	1

year	Numer of deleted
2014	1

I tried with this way https://www.sqlbi.com/articles/creating-a-slicer-that-filters-multiple-columns-in-power-bi/

but if I choose "2014" it shows:

year	Numer of implemented
2011	1
2014	1

Thanks!

[amitchandak · Accepted solution]

@mic_rys , I think you have to create a common year table and join it both years. One will be inactive, You can active that in measure using use relationship

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

This with date, you need for Year

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

New Table

Year = distinct(union(distinct(Table[implemented]),distinct(Table[deleted])))
Assume active join implemented

measure
implemented = countrows(Table)

deleted = calculate(countrows(Table), userelationship(Table[deleted], Year[implemented]))

[mic_rys]

the simpliest solution looks the best! thank you!