Forum Discussion
Microsoft Employee2 tables with same attributes but different values - how to join?
Hi all,
I imported 2 tables with the exact same attributes. the difference between these 2 tables is that the values are from different revenue streams; for the "ST table" i have revenue from Retailers and in the "SI Table" I have revenue from online sites. Is there a way to "join" these 2 tables? What I want to do is to be able to add filters to all pages, instead of having to use the same attribute but from different tables to filter on each page, while showing the corresponding revenue (i.e from retailers or online stores). does this make sense?
I have attributes: Product, Business, Region and Sub region in both tables
I want to filter by these attributes in all pages, but to be able to use the different revenue streams.
Any help will be much appreciated. Thanks!
The input which you have given does seem to match with output. If you need a common dimension please try it like this
Product = distinct(union(all('onlinetable' [Product]),all('Retail table' [Product])))
Looking at the structure I will suggest appending
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Append into a new table. You should get 5 fields as two-column does not have the same name. If required you can create a new dimension using Distinct.
if you need more help make me @
Appreciate your Kudos.
7 Replies
- Greg_Deckler
Community Champion
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
rilimonvHi all,
I imported 2 tables with the exact same attributes. The difference between these 2 tables is that the Revenue in "Online Table" comes from online sales and the revenue in "Retail Table" comes from retailers. this is how my tables are structured:
Product Region SubRegion Online Revenue Xxx North America Canada 100 yyy Latam Brazil 200 zzz Greater China China 300 Product Region SubRegion Retail Revenue aaa Latam LAH 400 yyy Europe Spain 500 ttt Greater China Hong Kong 600 What I am trying to do is to be able to use either of these attributes (Product, Region, SubRegion) as filters in all my pages in my Power Bi report, instead of having to use Product from Online table for Online revenue visuals and Product from Retail table for Retail revenue visuals (in the image below is where I want to be able to use these attributes as filters across my report in PBi)
I tried creating "dummy" tables and then creating a relationship between the dummy and the two tables. I used this to create the table:
Product = ALLNOBLANKROW('onlinetable' [Product])The table was created, and I did the same for the other attributes and created the relationship between "Online table" and "Retail table", however when I tried using them for the visuals, the numbers don't seem to cut the right way but instead show the same value across Products, regions and subregions. What I am getting in the table looks something like this:
Using the "dummy" table for Each of the attributes, and using "Online revenue" to illustrate:
Region Online North America 100 LATAM 100 Greater China 100 What I would expect is to get the right values, for example:
Region Online North America 91 LATAM 117 Greater China 88 Another example of what I would expect to be able to see is a table that shows attributes and revenue from tables, for example:
Region Online Retail North America 91 722 LATAM 117 99 Greater China 88 12 My guess is that I might not be "joining" the two tables the right way. Perhaps creating the "dummy" tables with the "ALLNOBLANKROW" function is not the right way to go.
I think it is important to mention that the only difference between both tables is the Revenue column. The attributes are present in both.
Is there someone out there that might know how to help me solve this?
Any help will be much appreciated. Thanks!
- amitchandak
Super User
The input which you have given does seem to match with output. If you need a common dimension please try it like this
Product = distinct(union(all('onlinetable' [Product]),all('Retail table' [Product])))
Looking at the structure I will suggest appending
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Append into a new table. You should get 5 fields as two-column does not have the same name. If required you can create a new dimension using Distinct.
if you need more help make me @
Appreciate your Kudos.
