Relationships between two columns

[Anonymous]

Hi!

A newbie to PowerBI, so sorry if the question is really basic, I just don't see the way out of what looks like a very simple problem.

I have some datasets with thousands of data, so I've simplified it to a very basic problem with two dimensions.

I have a dimension of sales and a dimension of salesman. Like this:

Sales table. Each row represents a sale, which may have up to two salesman involved. It also states the lenght of the sale.

Sales tableAnd then I have a table with the salesman names.

Salesman table

I want to get a the total time spent by each salesman: in total, as salesman 1 and as salesman 2. Ending up like this:

TOTAL time

John:10

Mike:35

Marta:0

Alex:40

Elizabeth:10

...

TOTAL time as salesman 1

John:10

Mike:20

Marta:0

Alex:40

Elizabeth:10

...

TOTAL time as salesman 2

John:0

Mike:15

Marta:0

Alex:0

Elizabeth:0

...

Does that make sense???

Thanks in advance!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

In addition to Greg_Deckler's method,

you could also transform the Sales table with Power Query as below:

go to "Edit Queries", select on "SalesmanId 1" and "SalesmanId 2", select "Unpivot columns",

Then close &&apply,

next, create relationship between two tables based on "Sales table"[value] and "salesman names"[SalesmanId]

Please find more details in my pbix.

Best regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Easiest way would be to not relate the tables and then create a measure that does two LOOKUPVALUE's and adds them together.