Forum Discussion
need help in editing relationship
dear all,
i have 2 data source, named
1) datasheet1
2) datasheet2
both sources has the column "productname", which i linked them in relationship.
I have another calculated column in both sources called "fiscalyear" but i am not able to link them in relationship.
How can I get it to show in my table, when a user select the filter for "productname" & "fiscalyear", it will display the total value from datasheet1 & datasheet2.
It is working for the value from “datasheet1” but it show the grand total of all the "fiscalyear" in datasheet2.
Sorry, that I am not able to provide any visual due to the data.
15 Replies
venalMemorable Member
Anonymous
Create a new column by concatenating the "ProductName" and "FiscialYear" in the 2 tables. (Ex:- Column-ID)
Remove existing relationships and create a new one by using the ID column.
If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and give it some kudos?
hi, it doesnt allow me to use concatenate? both fields are text field (though the fiscalyear is a calculated column).
Any advice?
Thank you.
- Ashish_Mathur
Super User
Hi,
The ideal solution in this scenario is create a third Table with a unique list of all Products and create a relationship from ProductName column of Table1 and Table2 to the single column of the third Table (all this can be done in the query editor). Likewise, from the FiscalYear column in both tables, we should create another column in each table with actual dates. Then we should build a Calendar Table and create a relationship from the Date columns of Table1 and Table2 to the Date column of the Calendar Table.
hi,
i have created a 3rd table with product name.
but i dont understand the part on fiscial year.
My first 2 table has a customlist of all dates, is that similar to the calendar table?
how do i go about with it.
would you advice. Thank you so much,
- Ashish_Mathur
Hi,
What is "customlist of all dates"? That is not a Calendar Table. Share the link from where i can download your PBI file.
- venal
Anonymous
Can you please share the PBIX file with the sample data and share the screenshots for the expected results.
- amitchandak
If possible please share a sample pbix file after removing sensitive information.
Thanks
