How to remove any applied filters in a RELATED column?

[Kolumam]

I have the below relationships and I am trying to remove all the applied filters to the Opportunity table. The below formula is written in Forecasted Energy Generation table.

Currency = RELATED(Opportunity[CurrencyIsoCode])

When I use the above, it only shows INR because it is filtering the opportunity based on the meter table.

See below pic for relationships.

 

Wrong Result here:

Correct Result:

[V-pazhen-msft · Accepted solution]

@Kolumam 

You can not return all values from that column, because there is no direct relationship between the two tables, any dax calculation must pass through the meter table to filter. In additon, you cannot add a relationship between opportunity table and Forecasted Energy Generation table, because it would be a loop.

To workaround, you can create a mid table, something like: 

mid table = SUMMARIZE('opportunity ,'opportunity'[Index],'opportunity'[CurrencyIsoCode])

Just make sure you have a column other than [CurrencyIsoCode], like [index] here that can create a relationship with Forecasted Energy Generation table. Finally, you can just use the RELATED('mid table'[CurrencyIsoCode])

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

You disable the interactions. That will remove all filters. Or take all date from that table only. Why related?

[Kolumam]

Hi @amitchandak 

I don't think this has anything to do with the interactions. So basically Forecasted Energy Generation Table and Opportunity are related indirectly. Issue is since I have the meter table in between, it is filtering the opportunities records based on the meter table and hence it only shows INR. First of all if I have to bring the column from another table, I have to use RELATED.

[TomMartens]

Hey @Kolumam ,

RELATED pulls one value, from the one-side of a relationship into all the related rows on the many-side of a relationship.

Please explain in more detail what the expected result should look like.

You can't use RELATED to generate more rows in the 'Forecasted ...' table.

Regards,

Tom

[Kolumam]

Hi @TomMartens 

As you can see from my above relationships, the meter table is filtering the opp records that go into forecasted energy generation table but I want to cancel out the filter and show all the records from opp in forecasted energy generation table. See above picture on the wrong result and the expected result and the calculated column that I have used in the forecasted energy generation table

[TomMartens]

Hey @Kolumam ,

the expected outcome is still not clear to me.

As I mentioned, you can't use a calculated column to create more rows in one table.

For this reason, create a pbix that contains sample data, but still represents your data model, upload the pbix to onedrive or dropbox and share the link. If you use an xlsx file to create the sample data upload the xlsx as well.

Describe the problem based on the sample data, and also describe the expected outcome/behavior based on the sample data.

Regards,

Tom