Combing graphs from 2 datasets on the same tab

[reh169]

So I am sure what I am trying to do has been done before. I am half way there and feel like I am just missing a small piece. I am graphing revenue and hours on the same tab but the data is coming from different datasets. My filters for the date are working because they both are using the calendar date parameters on their respective other tabs for CY and PY calculations. However, I also have filters for Company, region and yard. For those they are using the fields from their own datasets on their own tabs, but I do have a hierarchy table. When I join each of the respective tables company code to the company code in the hierarchy only one them is a solid line, and the other is a dotted line. When I go back to the tab, only the data set with the solid line adjusts with the company filter. What am I doing wrong? One of the reasons I am not using the hierarchy for filters on the tabs is that sometimes yards move regions and company's and then historically they in two locations and I end up with errors. 

[vivran22]

Hello,

I believe you should create support filter tables to make the filtering easy and simple to use. You may refer to the following video:

https://www.youtube.com/watch?v=ZrANsDNnZug

Thanks,

Vivek

[reh169]

So this works 2/3 of the way. I have to join 3 tables to my Company and Yard tables and for each of them it will not let me join them and make it active because it says there is a link between the company or yard table and the calendar table and there is no link between them for me to remove to make it work. I am at a loss.

[v-frfei-msft]

Hi @reh169 ,

USERELATIONSHIP should help you in your scenario by creating inactive relationship between tables. Please refer to this case.

https://community.powerbi.com/t5/Desktop/USERELATIONSHIP-issue/td-p/478744

If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[reh169]

But I am not trying to do a calculation I am trying to have filters work for 3 different data sets. I do not see how this resolves my issue.

[vivran22]

Is it possible for you to share the sample data file or snapshot of the relationship view?

Regards,

Vivek

[reh169]

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?

[PaulDBrown]

@reh169 

@reh169 

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.

[reh169]

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.

[vivran22]

Is it possible to share the sample data from both the tables along with the DAX you've created?

Vivek

[reh169]

So here is the dax I am using for my Calucations for current and prior year. 

Avg End Work to Export Date = CALCULATE( AVERAGE('Days to Invoice US All'[End Work to Exported Invoice]))

Avg End Work to Export Date PY = CALCULATE([Avg End Work to Export Date],SAMEPERIODLASTYEAR('Calendar'[Date]))

Below are screen shots of my data table, showing the Inv Created Date that I am using, How they are joined, and what the calendar table looks like. Is that what you were wanting? I have Current year and prior year calcuations on 3 or 4 other tabs and they are all working off of just the many to one join with the calendar table. But all of those are being run off of the job start date. But the date I am using should not matter right? I mean joining a date to date is the same regardless of what the date is named, right?

[vivran22]

Thank you for sharing the details.

I have a question regarding the date range available in the Days to Invoice US all table: What is the date range available in this? From the screenshot of the visual you shared earlier, it seems that when you are deselcting 2019, you are not getting any data.

[reh169]

So that result is when I have the Year and Month filters from the Calendar table as my slicers. I have it that way on my other tabs so that my prior year calucation will work. However, using them on this tab only seems to result in no data at all even with the tables being joined. This is what the data looks like when I use the actual inv created date from the days to invoice all table.

[PaulDBrown]

@reh169
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 try

[reh169]

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!

[PaulDBrown]

It 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?

[reh169]

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

[reh169]

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.

[PaulDBrown]

@reh169 
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).

[reh169]

I 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.