Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |