Only show years available in other table

[Anonymous]

Hi all

So i have 2 tables in my case. Table 1 contains data of ~10 years, and Table 2 only since 2018.

Table 2 contains revenue targets, Table 1 is the revenue table. These are connected via an inactive relationship.

This works just fine.

On the page i also have a "Year" slicer, this year comes from table 1, so it shows 2009-2019, but i only want to show years where targets are available, so for this case 2018 & 2019.

Wasnt able to create a column that fullfills my need.

Many thanks

[mussaenda]

Have you tried to make the relationship both?

Take note that this can slow down the report.

Thank you

[Anonymous]

Its already on "Both". Single direction didnt work for me (messes up the report)

@Anonymous  this looks pretty good, im getting small differences in my revenue though... But its possible that this is solveable inside my data.

[mussaenda]

another option is to drag the taget column your date slicer visual and filter it greater than or is equal to 0.

[Anonymous]

Hello @Anonymous 

I suppose you have selected table1 year column in slicer's field. Can you remove this field and drag-drop table2's year column?

Best Regards!

[Anonymous]

Yes i already tried that, but its not filtering the revenue correctly this way..

[Anonymous · Accepted solution]

can you refer to https://community.powerbi.com/t5/Desktop/DAX-query-to-compare-a-value-in-one-table-to-see-if-it-exis...

Column = CALCULATE(COUNTROWS(Table2);FILTER(Table2;Table2[year]=EARLIER(Table1[year])))>0

after use this formula, you can filter "true" columns