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.
Hello,
I was hoping you could help with the following. I have 2 columns called "Case Open" and "Case Closed" showing when each case was open and closed. I'd like to plot these 2 date series in a single graph using histogram, i.e. an histogram with Months as X-axis, and 2 separate columns showing how many open and closed cases I had in a particular month. I don't know why but if I simply use the histogram function no matter what I do, always the same numbers show for open and closed cases for each month, i.e. the two columns for each month are identical even tough the data is different for open & close, like below.
Thanks a lot in advance!
Case Open | Case Closed |
2-Jan-18 | 1-Feb-18 |
9-Jan-18 | 8-Feb-18 |
16-Jan-18 | 15-Feb-18 |
23-Jan-18 | 22-Feb-18 |
30-Jan-18 | 1-Mar-18 |
6-Feb-18 | 8-Mar-18 |
13-Feb-18 | 15-Mar-18 |
20-Feb-18 | 22-Mar-18 |
27-Feb-18 | 29-Mar-18 |
6-Mar-18 | 5-Apr-18 |
13-Mar-18 | 12-Apr-18 |
20-Mar-18 | 19-Apr-18 |
27-Mar-18 | 26-Apr-18 |
3-Apr-18 | 3-May-18 |
10-Apr-18 | 10-May-18 |
17-Apr-18 | 17-May-18 |
24-Apr-18 | 24-May-18 |
1-May-18 | 31-May-18 |
HI, @Anonymous
After my test, you may try this way as below:
In Edit Queries, select Case Open column and Case Closed column then click Transform->Unpivot columns
Then close&Apply
and darg new field value as X-axis, and Attribute field as legend
Best Regards,
Lin
Thanks! Just what I was loking for!
One minoe issue with my histogram is that each attribute on the histogram either starts late or finihses early,
ie Case Closed doesn't start from 0 from the earliest date (which is 1/2/2018 Case Open in example above).
Same for Case Open, this stops at the latest Case Open Date as opposed to the latest date in the histogram.
Is the a simple solution ofr this that I am missing?
Apologies for jumping on the thread.
Thank you. I'm not sure your solution would work in my case since my table is made up of 30+ columns with thousands of rows, one row each case.
I have several other visuals already setup that read from the other colums. With your solution I would effectively double the number of rows just for 1 column, and although the histogram would work, I would think all the other visuals will stop function the way they should?
hi, @Anonymous
For Case Open and Case Closed are two columns and put them together. it is bound to twice the size of the dataset.
and you could do these for the histogram visual and other visuals
Step1:
In the Edit Queries, duplicate basic table and then Unpivot columns for the duplicate table
Step2:
Create a relationship between the basic table with duplicate table ( optional )
Step3:
Use duplicate table only to create the histogram visual and basic table to create other visuals.
Best Regards,
Lin
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 |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |