Use of RELATED between cross source group relationship tables

[JensKlöker]

Hi there,

I created an encapsulated dataset for my company with daily exchange rates between some currencies. In order for being able to reuse this dataset, I put it to some company-wide dataset catalog, where high quality datasets are provided for reusage to many Power BI report creators.

Now, another use case specific dataset holds some prices in a foreign currency and in order to convert those prices to the concern currency, I connected to the first dataset, which holds the exchange rates.

After having a look into the docs I noticed, that I'm running against a limitation, because I want to use the RELATED function for the currency conversion in a cross source group relationship. E.g. the daily prices in the foreign currency are hold in one source group and the respective conversion rates from this awesome reusable dataset are hold in this second source group. As a result I'm currently not able to convert the foreign currency prices of the single rows in the use case specific dataset.

Of course I could just add the import queries from the exchange rates dataset into the use case specific dataset. However if something changes in the source of this dataset, all the changes have to be done in all the reports, which have copied the queries.

So my question is:

Does anybody have an idea how to solve this, without breaking the reusage concept? Maybe there is another approach without using RELATED but with using some different function, which is doing the same but works for those cross source group relationships?

Best regards!

[JensKlöker]

Hi @amitchandak ,

thank you for your fast response :-). In fact both data sources are datasets already, and therefore I can't add a column. Do you know a way how to do it with a measure, which could be created in the final dataset? What I call "final dataset" is the dataset which connects to the two datasets and has a relationship between the two.

In the first video you are explaining the approach I already tested. However the related function does not work, when the relationship is between different datasets.

However the LOOKUPVALUE approach might be interesting. Maybe it is possible to calculate some kind of imported bridge table in the final dataset, which holds the relevant exchange rate data. I'll try some things out.

[JensKlöker · Accepted solution]

In the meanwhile, I created an import table in Power BI Desktop as bridge table between the two datasets (source groups). In Power BI Desktop it works like expected, and I can use LOOKUPVALUE in order to add a calculated column, which calculates the prices in concern currency, by taking care about the foreign currency prices from the one end and the exchange rates from the other end of the "bridge".

The "bridge" table consists of two columns, which are both imported from the table which holds the prices. E.g. the primary key column (for looking up the price of the item) and a foreign key column (for looking up the exchange rates). The relationship between the price table and the bridge table should be 1:1 since the same primary key column is used for the relationship. However, 1:1 isn't accepted by PBI Desktop, which is kind of strang, but I can live with 1(price table) : n(bridge table).

In order to create a calculated column it is essential, that the dataset, in which you need to create it, is not of source kind "Power BI dataset", because due to the Live Connection limitations, you can't create it there. My idea of using an imported bridge table can overcome this limitation. There you can create a calculated column and use LOOKUPVALUE for that.

I think with the information given here, this can be marked as solved.

[amitchandak]

@JensKlöker , You use related, relatedtable, simple maxx/sumx/minx etc with filter to move data acorss tables 

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8