2 tables with same attributes but different values - how to join?

[rilimonv]

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!

[Greg_Deckler]

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

[rilimonv]

Hi 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!

[Ashish_Mathur]

Hi,

Perform these steps in the query editor

1. Create a column in the Online table with all cells in that column showing online.  Name this column as Channel
2. Create a column in the Retail table with all cells in that column showing Retail.  Name this column the same as mentioned in step 1
3. Now append the two datasets

Hope this helps.

[amitchandak · Accepted solution]

@rilimonv 

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.

[rilimonv]

@amitchandak 

Thanks for your suggestions! It sounds like appending is exactly what I need. The only thing I'm worried about is that these 2 queries have 16 columns each, of which 12 are same fields and 4 are different, so it might take a while to run 😃

One other question, if appending works, would I still require to create a new dimension? 

Another question on creating a new dimension using distinct - is that similar to what I tried with allnoblankrow? I'm asuming after creating the dimmension then I would have to create a relationship between the tables, and then create dimensions for each of the other fields, is that correct?

[amitchandak]

If appending work it can restrict no of dimensions. But You Should create few. Especially the one when you need some complex calculations. In case I have to use all. Ignore some fields.

allnoblankrow for the column will give distinct without null

https://docs.microsoft.com/en-us/dax/allnoblankrow-function-dax

In case a set of fields are in the natural dimension. Like City, State and City is you can create one single dimension using  summarize or distinct and selectcolumns

[rilimonv]

@amitchandak 

thanks! Appending my two tables worked and I have been able to use the same fields to filter across different visuals without the need to add them to each visual as filters.

For now, I will not add another dimensions as 1) I don't entirely understand what this would allow me to do and 2) i still don't get 100% on how to do this (is it adding a column with the distinct function you shared?)

Thanks again! I will be posting a new question regargind conditional formatting (I have cards with conditional formatting but when I publish to my workspace online, the formatting wont work. However it works perfectly on PBi desktop)