Table join - multiple columns

[bo_afk]

I have two tables joined together on 3 columns

1. Market

2. WeekNumber

3. Division

Using the columns from one table only, I'd like to filter the data for a metric in the other table. However, when I apply the filter, the metric doesn't change. I realised that the data only chages for the column where I've marked the join as "make this relationship active", but i can only do this for one column.

How can I filter the metrics based on all 3 columns of the other table?

Thanks

[v-chuncz-msft · Accepted solution]

@bo_afk 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/composite-primary-key/td-p/307604

[amitchandak]

@bo_afk ,

I think you want to join on more than one column. Create a concateneated column in both tables like this 

key = Market & "-" & WeekNumber &"-"& Division

and join. 

Power BI mutiple joins are there to change relation using userelation. You can check my HR Anaytics blog for that. Link is in signature.

[v-chuncz-msft · Accepted solution]

@bo_afk 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/composite-primary-key/td-p/307604

[bo_afk · Accepted solution]

Hi @v-chuncz-msft , thanks for the link.

I actually ended up doing something similar by merging the columns I wanted to join and then join the tables on these merged columns.

[vivran22]

Hello @bo_afk ,

You need to create measures by using dax : USERELATIONSHIP

This will help you to filter your visuals accordingly.

If you could share the sample data along with expected results, then we could provide a better solution.

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[bo_afk]

Hi @vivran22 , thanks for your response.

Could you kindly walk through how I'd use this?

[vivran22]

@bo_afk 

Is it possible for you to share the sample data file? It will help in creating specific solution in this context

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn