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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors