Forum Discussion
Combing graphs from 2 datasets on the same tab
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!
Community ChampionAs 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
I know it’s a pain but it would be very helpful to have a sample of the fact tables you need to work with. Otherwise it is a bit of a shot in the dark.
If you are getting blanks, it can be for a number of reasons, including the fact that your lookup tables do not include all possible values from your fact tables, depending on what fields you are using in visuals etc... I can create a mock-up, but if the issue is what I’ve just explained, it will be of little use.
So... if possible, please provide a sample of the fact tables you need to reference. All of them. Then we can take it step by step.We need you to help us help you, basically
PS: just so that you might understand my confusion, a few posts back you mention 3 data tables, and more recently you mention 4. Confession: I am lost as to what your basic fact tables are and exactly what fields you are trying to relate. I am happy to help, but I need to understand your scenario. Please provide examples in the form of tables (I'm not concerned about the bridge/lookup tables; I need to understand the data or fact tables you are trying to work with).
So you are saying I do not have to link the date, year, month and quarter fields from the data table to the calendar, just the date and it will work?
My company table looks like this, EWS, RMSC, INF, SCR, and ACR. All my data tables have some or all of those. My yard table is a list of cities where we have yards. Again the data tables have some or all of the yards depending on the data.
Does this answer your questions?
- PaulDBrown@reg169
That’s correct. It will work just by establishing a one-to-many relationship between the date field in your calendar table to your fact tables. The same applies for any other field you wish to filter tables by using bridge tables. That’s how PBI is designed to work
Thank you for the info on your tables. Can you please explain if they are values or columns?
As I understand it, you have a revenue table and a forecast table. What’s the third fact table. Also, what fields are common in each table?
I will try to create a basic mock-up with the info you provide to illustrate what I’m trying to explain Ok I tried that and it seems to work for 2 tables and then the next two tables that I join based on date it doesnt work. I have a total of 4 tables linked in a many to one relationship with the calendar table and only 2 of them work. The other two when I select date fields everything goes blank.
The fields in question are all columns from my queries. Each query represents a different data set. One is about jobs, one is about utilization, one is comparing budget and revenue. All of them have Company and Yard columns, as well as Month and year date columns. All of the tabs that they have on their own have filters for Company and Yard as well as date fitlers. I have been requested to make an executive tab that combines graphs from all 3 tabs. I created the Yard and Company tables as a distinct list of the companies and yard and it works for 2 of the 3. Something with having more than 2 tables joined in a many to one relationship. Not sure but I am so ready to bang my head on the wall.