Second relationship - based on condition

[Turnipface]

Hi PowerBI enthusiasts,

I'm stuck on the following problem that can be explained with the mock data below:

Dataset 1

Product	Machine ID	Date
Cappuccino	5	01-Jun-17
Espresso	5	01-Jun-17
Coffee	5	02-Jun-17
Coffee	5	02-Jun-17
Coffee	5	02-Jun-17
Cappuccino	10	01-Jun-17
Cappuccino	12	01-Jun-17
Coffee	13	02-Jun-17
Espresso	1	02-Jun-17
Cappuccino	1	03-Jun-17
Espresso	1	03-Jun-17
Cappuccino	1	03-Jun-17
Cappuccino	14	02-Jun-17
Espresso	4	03-Jun-17
Coffee	4	03-Jun-17
Cappuccino	4	03-Jun-17
Cappuccino	5	03-Jun-17
Coffee	5	03-Jun-17

Dataset 2

Date	Country
01-Jun-17	Germany
01-Jun-17	Italy
02-Jun-17	UK
02-Jun-17	France
03-Jun-17	Hungary
04-Jun-17	Romania
05-Jun-17	Portugal

 Since Many to Many relationship is not possible, i've created a separate file that only has the dates and serves as a bridge to link them together.

This works well, but i want to only relate the Country to the Product field if the Machine ID is a specific one (5, for example).

So even if the dates might be the same in some cases and yield a valid link, i only care about the data if it's concerning Machine ID 5. 

How do i create this condition for the relationship?
Many thanks in advance!

[v-huizhn-msft]

Hi @Turnipface,

For example, the Machine ID is 5. We For Date 01-Jun-17, there are two products(Cappuccine, Espresso), and there are two countries(Germany and Italy). How to create it, what's results do you want to get? Could you please share more details for further analysis?

Best Regards,
Angelia

[Turnipface]

Hi Angelia,

I suppose i'll be happy if both Germany and Italy are accossiated with the ID and Date.
And this is the case if i make a relationship between the Date.

However, it's also counting the Cappucinos for ID 10 and 12 that were also on 01-Jun-17 and this is what i don't want to happen. The relationship should only be valid if both the Date and the ID are the same. If i add ID 5 on every row on Dataset 2 (next to the Country data), will there be a way to do it from that?


Edit: This is what the current relationship looks like:

Regards,

Stefan

[v-huizhn-msft]

Hi @Turnipface,

In your Bridge table, please create a calculated column to get the country using the formula.

Country = CONCATENATEX(Country Date,Country Date[Country] & ",")

Then in your Machine Data, create a calculated column using the formula.

Country=RELATED(Bridge[Country])

Best Regards,
Angelia

[Turnipface · Accepted solution]

Ah, got it! 

It was as simple as filtering the visual with the Country data by the Machine ID (5 in this example).

@v-huizhn-msft Thank you for your willingness to help with this basic question 🙂 
But i couldn't rest so i kept poking this problem.

Marking the thread as solved then.