cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Turnipface Regular Visitor
Regular Visitor

Second relationship - based on condition

Hi PowerBI enthusiasts,

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

Dataset 1

ProductMachine IDDate
Cappuccino501-Jun-17
Espresso501-Jun-17
Coffee502-Jun-17
Coffee502-Jun-17
Coffee502-Jun-17
Cappuccino1001-Jun-17
Cappuccino1201-Jun-17
Coffee1302-Jun-17
Espresso102-Jun-17
Cappuccino103-Jun-17
Espresso103-Jun-17
Cappuccino103-Jun-17
Cappuccino1402-Jun-17
Espresso403-Jun-17
Coffee403-Jun-17
Cappuccino403-Jun-17
Cappuccino503-Jun-17
Coffee503-Jun-17

 

Dataset 2

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

 

 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Turnipface Regular Visitor
Regular Visitor

Re: Second relationship - based on condition

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 Smiley Happy 
But i couldn't rest so i kept poking this problem.

 

 

Marking the thread as solved then. 

4 REPLIES 4
v-huizhn-msft Super Contributor
Super Contributor

Re: Second relationship - based on condition

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 Regular Visitor
Regular Visitor

Re: Second relationship - based on condition

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:

Capture.PNG


Regards,

Stefan

Turnipface Regular Visitor
Regular Visitor

Re: Second relationship - based on condition

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 Smiley Happy 
But i couldn't rest so i kept poking this problem.

 

 

Marking the thread as solved then. 

v-huizhn-msft Super Contributor
Super Contributor

Re: Second relationship - based on condition

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