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.
Hello everyone,
I am having a hard time understanding how relationship work, what they do, etc
My situation is as follows: Say I have a query containing sales data at the geographic level, and another one at the product level. Both contain the exact same date rows.
My key question is: What kind of relationship should I create to have a dashboard in which selecting a specific date filter changes the visualization content for both the product and geographic level?
What made sense spontanously was to create a relationship from date of query 1 with date of query 2, but that's not the way it works. Also, I'm not sure I understand the difference between many to one, one to many, both and single, etc.
Thanks in advance!
Solved! Go to Solution.
Hi @Arcturus
My first thought would be to go for
Then create the following relations
That should get you going, and then later you can flesh it out, maybe by adding a region to your Location table, or adding a new Salesperson table and a salesperson id to the Sales table.
Hope this helps,
Chris
If you want a date table then you should have a single row per date. The error suggests that the date table has duplicate dates.
If I'm understanding your predicament correctly, just create a date table and relate the date to each of the dates in the other two fact tables. That takes care of the date filtering.
You can also create lookup tables for geography and for product (and relate them to each corresponding fact table), and use theses to filter in slicers, matrices and visuals in general.
Proud to be a Super User!
Paul on Linkedin.
Thanks @PaulDBrown,
I might very well be doing it wrong, though when I try to create a relationship between my Date table (which only comprises a Date column) and the Date columns from my other Queries, I get the following error message:
You can't create a relationship between these two columns because one of the columns must have unique values.
What am I missing?
If you want a date table then you should have a single row per date. The error suggests that the date table has duplicate dates.
Hi @Arcturus,
It sounds like you need to find a common field between your geographic sales and product sales data and then filter by the sales date from one of the tables.
Do your sales have a unique sales id that occurs in both tables?
Chris
Thanks for offering help @Chris99, much appreciated!
Currently there is none (I am training on fictious data sets that I have created).
Could it be done w/o one? Maybe by connecting the dates, which are 100% identical in each query and occur in both.
If not, could you please confirm my understanding of the way it would work:
- I would need to create an extra identical column in each query table, containing Sales ID.
- I would need to create a relationship linking the Sales ID from the geographical query, to the Sales ID of the sales query (Not sure this is right, as I recall not being able to create relationships between identical fields)
- I would need to set the relationship to be a one to one, as each Sale ID will be unique
- I would need to set the relationship to be both cross-filtering as there is information from both table to be used in the visualisations.
Thanks again!
Hi @Arcturus
My first thought would be to go for
Then create the following relations
That should get you going, and then later you can flesh it out, maybe by adding a region to your Location table, or adding a new Salesperson table and a salesperson id to the Sales table.
Hope this helps,
Chris
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |