Forum Discussion
Combing graphs from 2 datasets on the same tab
Community ChampionIs it possible for you to share the sample data file or snapshot of the relationship view?
Regards,
Vivek
Not sure if this will help. So I have 3 data sets on one page. I have two them working, the third one it will not let me create another many to many relationship. So my Jobs and Utilization tables are working and joined to the calendar table and the company and yard table. This enable both to filter correctly. My budget and revenue table it will not let me join to the company, yard or calendar table. As you can see I have added another calendar table but it is only letting me make one join there. Does this help?
PaulDBrownAnonymous
The general consensus is to avoid many-to-many relationships where possible (it can wreak havoc in calculations...)
Can you not create lookup/dim tables as bridge tables between the different fact tables by linking common fields (and removing duplicate values to enable one-to many relationships)?
If you can provide sample fact tables with dummy data/values I will happily give it a tryI created a 2 bridge tables, my company and yard tables, that are many to one with just the company and yards listed in the bridge tables, but I need to link 3 tables to them and it will not let me do more than 2. When I try to make the relationship active I get this error. And what makes no sense to me is that the Yardname table and the Calendar table are not linked at all in any way. There is no way to link them.
My other issue is that I am trying create Current Year and Prior Year calculations. To do that I am having to link my date, to the date table which is many to one, then my slicers are year and month and sometimes quarter and those end up being many to many and after a bit it will not let me add anymore. Can you think of away around that? I am all ears. Thank you!
- PaulDBrownIt would be very helpful if you could provide examples of your tables (even if they are only a few rows of dummy data and fake names for company etc). It will help to create a mock-up of the model.
As regards your last comment regarding the calendar table linked by date in a single-to many relationship to the other date field in the fact table, but the year or quarter slicers are many-to-many... I don’t follow you. If you have a good calendar table, with columns for Month, quarter, year etc and it is linked in a one-to many by the date fields, you can use the month,quarter, year columns in slicers without a problem: selecting a Year in slicer from the calendar table will filter the tables related by the group of dates in the same rows as the column you are filtering. It works beautifully, and that is one of the reasons It is recommended to use a calendar table (or any other bridge table for that matter). This is the way PBI is designed to work. By doing this, you are in effect creating the filter context common to all related tables, allowing for correct calculations between equivalent fields in different tables (for example, actuals in one table compared to target or forecast from another table
Does that make sense?
- PaulDBrown
Anonymous
Anonymous
BTW, looking at the screenshot of your model, I think you are confused about how relationships, lookup tables, and filter conexts work. Just as an example, it is VERY rare to have more than one relationship between two tables (generally you only need more than one relationship between two tables if for example your fact table has two date fields - for example, purchase date and delivery date).
And it seems you have two or three stemming from one table to another in a few tables.
So I cleaned everything up and now it is letting me make the joins and everything is working as it should on my combined tab. Which is great. I have one last issue that is not working as expected. I have my Invoice Created date and I have linked that in a many to one relationship with the date field in my calendar table. It is active. I have made the Year and Month columns has my slicers on my tab, but whenever I select them all of my data goes blank. I went and reformatted the date so that it was no longer date time just the date, I have removed the link and added it back but nothing is working. I really am at a loss.
- vivran22Is it possible to share the sample data from both the tables along with the DAX you've created?
Vivek