Creating a clustered chart using two date values

Question

Hello Everyone,&nbsp;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.&nbsp;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.&nbsp;

anonymous · Answer

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

vanessafvg · Answer

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&nbsp; date table that is denoted by a unbroken line, that is usually the first&nbsp; 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 videoshttps://www.youtube.com/watch?v=sONvctPlplY&amp;vl=enotherwise 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.

amitchandak · Answer

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 · Answer

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

vanessafvg · Answer

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

Forum Discussion

Creating a clustered chart using two date values

6 Replies

Related content

How to create a Clustered, Stacked Column Chart

Cannot create liquid clustering on existing unpartitioned table

Clustered column chart incomplete legend

How to create measure to get values clustered

Need a stacked clustered column chart