Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Only show years available in other table

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

1 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

View solution in original post

6 REPLIES 6
mussaenda
Super User
Super User

Have you tried to make the relationship both?

Take note that this can slow down the report.

Thank you

Anonymous
Not applicable

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

 

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

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

Osmanakgunduz
Helper I
Helper I

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
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.