Creating a relationship between two queries refering the same database view

[Sylvain74]

Hi,

The title of my request could seem a bit strange so, please find here below more details about it.

Let's say I have a database view with several columns and amongst them there are these columns "item_type", "amount" and "quantity". The column "item_type" can have 5 different values.

First I have created a pivoted query in PBI and used "quantity" column to aggregate.

Secondly I have created another pivoted query and used "amount" column to aggregate.

Now in my report, I would like to add a table including data coming from both queries, but I do not see how to create a relationship between the two queries as I was not able to find a column with unique value in both.

Could you please help me ?

Thanks

[Greg_Deckler · Accepted solution]

Merge query might get you there, however another technique is to simply create a third query and call it "Whatever". Point it to the same data source but remove all other columns other than whatever you wish to link on, like DestinationCountry it looks like from your data. Click on the remaining column and remove duplicates. Import.

Now you have a unique list of item types that you can wire to both of your other tables. In your table, select the single column from your third import and the fields you want from your first and second tables.

That should get you what you want.

[HarrisMalik]

Can you provide the snapshot of your views? if it is just aggregation based on item_type why don't you use a single table?

Regards

Harris

[Sylvain74]

Hi Harris,

Please find below the printscreen. In this query I pivoted the field "item_type" into columns and aggregated on field "quantity"

The pivoted columns are "01.Contract", "02.Elements",...

I did as well the same thing in a second view but I aggregated on "Amount" column and now I would like to "combine" these two queries in order to use columns from both in the report.

[HarrisMalik]

Hi

To me it doesn't look like a good design. Why you want to use item_type as measure? This way every time you have a new item_type you will get a new measure. Why don't you keep item_type as dimension and Quantity and Amount as two measures. This way you can simplify the design and have everything in one table rather than merging them. Secondly in future if you get new item_types they will be added as new dimension members and will not effect the overall model.

Regards

Harris

[Anonymous]

have you tried "Merge Queries" option?