Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Creating a clustered chart using two date values

Hello Everyone,

 

I am currently trying to create a clustered bar graph with three types of bars, Number of requests made in a month, number of those requests that have been completed (not necessarily in the same month), and total number of requests closed in the month (not necessarily opened in that month). I have the first two fields working properly, but the third is giving me trouble because date closed and date opened are tied to the same ID, so it needs a seperate date axis basically. I have tried a number of workarounds with no luck yet.

 

According to excell, and I have verified this by creating a seperate chart, my total complete should be 74 for april, 180 for may, and 42 in june. I don't know where I am losing values. Any help would be greatly appreciated.

help.PNG

 

6 REPLIES 6
amitchandak
Super User
Super User

@Anonymous , You can join both dates with a common date dimension. One join will be inactive. You can use userelation to activate join.

refer to this blog how to deal with blog - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Anonymous
Not applicable

Thank you for the suggestion. Unfortunatly this had no effect on my values. Somehow I don't think they are connecting properly. 

can you share your relationship view and the fields the joining on?




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

Table is my date table, Query 1 is the set of dates that I want to alter. Metrology Request is my main table. I want to show created per month and completed from that month based on the values in metrology request. I want to show completed by month based on the data in Query 1. I have a graph currently functioning for created by month and completed from that month. that is using the date in the date table as an axis. Unfortunatly when I add in the completed data from Query 1, the numbers are not what they should be.

 

Screenshot (1).pngScreenshot (3).png

ok in the solution that Amit shared, there is an example of the userelationship() function did you look at that?

when you have more than one relationship on a table linking to the date table you will have one primary(active) relationship the  date table that is denoted by a unbroken line, that is usually the first  date you link on, and then every other date you link to the date table will have a dotted line(inactive). the dates with the dotted lines will need to use the userelationship() function in the their measures to explicitly tell power bi not to use the default which is your primary but to use the 'inactive' dates.

when you have one date table and you link that date to different dates in one transaction table, you need to use the above method. creating another table and i think also linking with bidirectional relationships is possibly what is giving you issues.

my suggestion is to keep all of the dates in the original table. create all the relationships you need (so for each date link it to your date table), and then use the userelationship() function to generate the measures for the inactive relationships.

curbal has very thorough explanations in her videos

https://www.youtube.com/watch?v=sONvctPlplY&vl=en

otherwise if you share your data/ dummy data in text form or the pbix i can set it up and you can maybe understand that way.






If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

This worked. I was honestly starting to think it couldn't be done. Thank you!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.