Displaying a related row in same table, and in another table without an active relationship

[dusdau]

Hi All,

I am working on a data model and I have it pretty much where I want it, however I have a business need that I can't seem to meet without completely duplicating data into separate tables.

Here is the model in question:

I want to be able to select a row in the Sales fact and be able to cross filter another table to show:

1) The related price history row where PriceHistKey = PriceHistKey (I also need to be able to show the entire price history for the Part, so I need to have that relationship remain active)
2) The related Sales row where SalesKey = LastSaleKey.

I know I could essentially duplicate the Sales and Price History tables and use a bi-directional relationship but I would prefer not to create bi-directionals and also would prefer not to duplicate the sales and price history tables as they are larger tables.

Anyone know a way to accomplish this?   It seems like it should be doable without duplicating tables.

Thanks,

Dustin

[v-deddai1-msft]

Hi @dusdau ,

You can contain the following formula in your measure or visual level filter:

CROSSFILTER('Sales'[SalesKey],' price history'[SalesKey],Both) 

For more details, please refer to https://docs.microsoft.com/en-us/dax/crossfilter-function 

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

Best Regards,

Dedmon Dai