How to create Clustered Column chart with multiple year dimensions

[sanman]

I want to show the number of items created by year and number of items closed by year on the same clustered column chart. How can I do that?

[v-jingzhang · Accepted solution]

Hi @sanman 

Both of the above solutions can successfully achieve what you want. I illustrate both in my understanding with images below in case you need it.

Solution 1:

1. In query editor, select CreateYear and ClosedYear columns and unpivot them.

2. Create a clustered column chart like below.

Solution 2:

1. Create a year table, then create an active relationship ‘Items (2)’CreateYear(*) – ‘Year’Year(1) and an inactive relationship ‘Items (2)’ClosedYear(*) – ‘Year’Year(1).

2. Create a measure using Userelationship function as below.

Count of Closed = CALCULATE(COUNTROWS('Items (2)'),USERELATIONSHIP('Items (2)'[ClosedYear],'Year'[Year]))

3. Create the clustered column chart.

Best Regards,

Community Support Team _ Jing Zhang

[amitchandak]

@sanman , Create a year table join it with both Create year and close year. One will active another one will be inactive. Use userelation to in the measure to change date

This video has an HR example - but applies here too -

https://youtu.be/e6Y-l_JtCq4

Refer

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[Greg_Deckler]

@sanman - I *think* you need to unpivot your year columns. Use Attribute as the legend, Value as the axis. Count of itemname for value