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.
Hi All,
I have some data like below. I want to show a monthly comparison of number of Orders received vs orders delivered in a clustered column chart.
Incident | Order Date | Delivery Date |
Order1 | 9/12/2018 | 9/13/2018 |
Order2 | 9/13/2018 | |
Order3 | 9/18/2018 | |
Order4 | 9/19/2018 | |
Order5 | 9/20/2018 | |
Order6 | 9/21/2018 | |
Order7 | 9/24/2018 | 10/21/2018 |
Order8 | 10/5/2018 | 10/31/2018 |
Order9 | 10/6/2018 | 10/11/2018 |
Order10 | 10/8/2018 | 11/12/2018 |
Order11 | 10/9/2018 | 10/11/2018 |
Order12 | 10/9/2018 | 10/11/2018 |
Order13 | 10/10/2018 | 10/11/2018 |
Order14 | 10/10/2018 | 11/6/2018 |
Order15 | 10/11/2018 | 10/11/2018 |
Order16 | 10/12/2018 | 11/4/2018 |
Order17 | 10/16/2018 | 11/14/2018 |
Order18 | 10/17/2018 | 11/4/2018 |
Order19 | 10/22/2018 | 11/4/2018 |
Order20 | 10/24/2018 | 10/25/2018 |
My problem is which date column should i choose to create the month column to show the correct data.
I am looking for something like below which i can then represent in a clustered column chart however. At this moment if i create month from either of the dates then the data will be incorrect. I am already using the date table and using the month column from date table itself.
Month | Ordered | Delivered |
Sep-18 | 7 | 1 |
Oct-18 | 13 | 8 |
Nov-18 | 6 | |
Total | 20 | 15 |
Please help.
Solved! Go to Solution.
To accomplish this, you'll want to make sure that you have a date dimension table that you can link both columns to. One of them will be inactive because you can only have 1 active relationship between 2 tables, and that's ok.
You can make use of the USERELATIONSHIP function to use the inactive relationship for the measure to sum the item that's related to that date.
For example, lets say you connect both dates to the date dimension table and you want the order date to be the active relationship and the Delivery date to be the inactive date. You would then write 2 different measures for Ordered and Delivered.
Ordered =
COUNTX(table, table[Incident])
Delivered =
CALCULATE (
COUNT( table[Incident]),
USERELATIONSHIP(calendar[date], table[Delivery Date]))
Then you can create a table visual with the date from the date dimension table as the axis and the 2 measures you just created as the values.
Create date table which is disconnected with main table.
@Anonymous you are right i tried this solution and this is not working because i am only able to create a bi directional filtering for 1 out of 2 data tables, with other one i am getting ambiguity error hence Powerbi doesnt allow me to do it.
Moreover i am quite surprised that its not possible because its quite a common thing and i was able to do in Tableau all fine.
Hi there.
First, I want to say that the correct solution to the problem has already been given in this thread. It's the one based on an INACTIVE relationship from a Date table to one of the date columns. This has always worked OK and it will now if properly understood and implemented.
But to do it properly one has to 1) know how to handle inactive relationships and 2) how to create a proper Date table.
By the way, there's no need to have bi-directional filtering in this case at all. What's more, bi-directional filtering is (almost) never the thing to do. It should be used only when there's no other way to achieve what one wants with good standard techniques and when all the consequences of this technique are well understood by the modeler. Good advice is to stay away from bi-directional filtering unless you're experienced and know all the pitfalls.
Please implement the solution based on active/inactive relationships from a Date table to the fact table.
Best
D
To accomplish this, you'll want to make sure that you have a date dimension table that you can link both columns to. One of them will be inactive because you can only have 1 active relationship between 2 tables, and that's ok.
You can make use of the USERELATIONSHIP function to use the inactive relationship for the measure to sum the item that's related to that date.
For example, lets say you connect both dates to the date dimension table and you want the order date to be the active relationship and the Delivery date to be the inactive date. You would then write 2 different measures for Ordered and Delivered.
Ordered =
COUNTX(table, table[Incident])
Delivered =
CALCULATE (
COUNT( table[Incident]),
USERELATIONSHIP(calendar[date], table[Delivery Date]))
Then you can create a table visual with the date from the date dimension table as the axis and the 2 measures you just created as the values.
Hi,
I am already using a date dimention table and using the month column in the visual created from that table itself still however the numbers arent correct. This seems quite tricky however i am sure its quite common scenario too.
Did those DAX measures work? @Anonymous
@Anonymous that worked, however for some reason when i split it by a "Monthyear" field i created from 'date' field from Date table and then make sure that the numbers are correct i compare the numbers in another table but this time by 'MonthYear' field created from a date field from the data table (imagine this is our "delivery date') the monthly split of numbers do not match. See the screnshot below.
Consider the month on the right table as 'delivery date' derived from the data table and and left is monthyear derived from date field of the date table.
Hi,
No they didnt , and i have also updated the post with some more info for others.
See this sample for a solution that appears to accomplish what you're looking for. Also, note that your sample set of data don't share the same years in Order Date vs Delivery Date.
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 |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |