Filter table visual on both active and inactive relationship

[Nielf]

Hello, 

I have a data model like the one below, where there are two relationships between the Account table and the opportunity table. Customer (parentaccountid) is the active relationship, and the other is End Customer (sem_end_customerid) - the inactive relationship. 

My end goal is to filter a table visual with opportunities, where the selected account(s) are either Customer OR End Customer in the opportunity table.  

I thought I had found a solution where I used the measure below, and added the measure as column to the table visual. This now shows me all the opportunities, where the Account is either Customer or End Customer. 

End Customer Filter = 

CALCULATE(
    COUNTROWS(Opportunity),
    CROSSFILTER(Account[Account], Opportunity[parentaccountid],None),
    USERELATIONSHIP(Account[Account], Opportunity[sem_end_customerid])
    )

However, the issue with this solution is, that columns from the Opportunity finance table are blank in the table visual if only the End Customer is equal to the selected Account(s). 

I hope you can help. 

[Anonymous · Accepted solution]

Hi @Nielf 

This is because when you drag columns from the Opportunity finance table into the table visual, it is still filtered by the default active relationship passed by the Account table and the Opportunity table. If you want to see results based on both relationships, you still need to create a similar measure to activate the inactive relationship to get corresponding results from the Opportunity finance table. Inactive relationships can only be used in measures. It cannot be passed in a visual invisibly. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Nielf]

@Anonymous 

It works partially for me. I have created another measure, which combines the value from the active and inactive relationship, which I can display in th table.

Combined sales price (base) = 

IF(ISBLANK([$ All opportunities]),
[End Customer Sales Price (Base)], [$ All opportunities])

While it works on row level in the table, the total is wrong, since only the active relationship seem to used there. Do you know if it's possible to work around this? Total should be 213.604.374.

[Nielf · Accepted solution]

@Anonymous thanks for the swift help. That makes sense. 

[Anonymous · Accepted solution]

Hi @Nielf 

This is because when you drag columns from the Opportunity finance table into the table visual, it is still filtered by the default active relationship passed by the Account table and the Opportunity table. If you want to see results based on both relationships, you still need to create a similar measure to activate the inactive relationship to get corresponding results from the Opportunity finance table. Inactive relationships can only be used in measures. It cannot be passed in a visual invisibly. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!