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.
Hello everybody,
my report in Power BI Desktop currently consists of 2 excel sheets that are linked by a relationship (m:n) with double-sided cross-filter direction. The common criterion is the “order number”.
Table 1 contains the columns “Order Number”, “Occupancy Time” and “Date”.
Table 2 contains the columns “Order Number”, “Quantity” and “Date”
In addition, the column with the date is divided into a Hirachie -> year month day If I want to display in a report the sum of the amount with the sum of the occupancy time per month in the form of a bar chart and additionally want to provide the user with a filter for selecting the month, I would have the two columns “Date” of Table 1 and Table 2 merge in the background. is a merging of the two date columns in the background somehow possible, so that only one filter can be used???? Alternatively, I would have to show in the report two individual filters, which must be selected in each case with the same month by the user.
Solved! Go to Solution.
Hi @HT123 ,
Make sure you have a date table.
Here's a video from Guy in a Cube about it.
Regards,
Fernando
HI Fernando,
I don't think I explained my problem properly. Here again the actual situation presented wisely
Here are my 2 Excel Sheets, the relation and the visual
The bar chart is intended to show the sum per month. The quantity is correctly displayed. Unfortunately, time is wrong. There should be a time a month of 4 coming out not 20! Is this somehow possible??
I would still need a relation from Table 1 Column "Date" to Table 2 Column "Date."
Hi @HT123 ,
I replicated your sample data, created a date table, and created a dim table for orders like so:
I did not create a relationship between table A & B on Orders, instead I created a relationship of each table with the dim order table above.
I created a simple measure in each table:
Total Qty = SUM('Table A'[Quantity])
Total Time = SUM('Table B'[Time])
This is the result:
Hope it helps!
Regards,
Fernando
Hi @calerof ,
Thanks for your quick help. I would have thought that there would be no solution to this.
The community is a great thing.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |