Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi,
Download my PBI file from here.
Hope this helps.
If possible please share a sample pbix file after removing sensitive information.
Thanks
@Anonymous
Can you please share the PBIX file with the sample data and share the screenshots for the expected results.
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,
Hi,
What is "customlist of all dates"? That is not a Calendar Table. Share the link from where i can download your PBI file.
@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.
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?
Hi,
Download my PBI file from here.
Hope this helps.
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.
Hi,
I doubt I'll be able to help in that case.
if it got to make the changes in dataset, how should it look like...
It would definitely help in the future.
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |