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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.