Forum Discussion
Date Filter
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.
5 Replies
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
