cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
B_Adams Member
Member

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

Accepted Solutions
Osmanakgunduz Regular Visitor
Regular Visitor

Re: Only show years available in other table

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
Osmanakgunduz Regular Visitor
Regular Visitor

Re: Only show years available in other table

Hello @B_Adams 

 

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!

 

B_Adams Member
Member

Re: Only show years available in other table

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

Osmanakgunduz Regular Visitor
Regular Visitor

Re: Only show years available in other table

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

mussaenda New Contributor
New Contributor

Re: Only show years available in other table

Have you tried to make the relationship both?

Take note that this can slow down the report.

Thank you

Highlighted
B_Adams Member
Member

Re: Only show years available in other table

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.

mussaenda New Contributor
New Contributor

Re: Only show years available in other table

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

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors