need help in editing relationship

[Anonymous]

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.

[amitchandak]

If possible please share a sample pbix file after removing sensitive information.
Thanks

[venal]

@Anonymous 

Can you please share the PBIX file with the sample data and share the screenshots for the expected results.

[Ashish_Mathur]

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.

[Anonymous]

@Ashish_Mathur 

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.

[Anonymous]

@Ashish_Mathur @amitchandak 
hi all
please see attached

The value are correct for table Xvalue but not for table Octvalue.
How can i make them using the same fiscal filter?

Thank you so much.

[Ashish_Mathur]

Hi,

Does the FY run from December to November?  Also, the best would be to append data from both datasets into a single one and then build your Pivot Table.  Would you be amenable to this approach?

[Anonymous]

@Ashish_Mathur 

Here the files

https://gofile.io/?c=z2BwFr

[Ashish_Mathur · Accepted solution]

Hi,

Download my PBI file from here.

Hope this helps.

[Anonymous]

@Ashish_Mathur 

in this case, the fy is from april to march.

is there any way to use the current pbix file to make it work or add calendar?

I won't be able to make changes to dataset.

[Ashish_Mathur]

Hi,

I doubt I'll be able to help in that case.

[Anonymous]

@Ashish_Mathur 

if it got to make the changes in dataset, how should it look like...

It would definitely help in the future.

[Ashish_Mathur]

Hi,

I don't think any changes in the dataset are required.  In order to try to help you, I may need your PBI and your source Excel files from where you imported data into PBI.

[venal]

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

[Anonymous]

@venal 

hi, it doesnt allow me to use concatenate? both fields are text field (though the fiscalyear is a calculated column).

Any advice?

Thank you.