Forum Discussion
Problem with Clustered column chart - wrong output report when multiple data in Values
Hello everyone,
I'm having a big issue with Clustered column chart.
I created several report based on different tables (TABLE_A, TABLE_B, TABLE_C, ecc. to simplify... each one has a MEASURE_A, MEASURE_B, MEASURE_C, ecc.)
Individually, the single reports work fine... the problem is when I try to put different values in the same Clustered column chart.
Example scenario, the TABLE_A looks like this:
The DATE_A column is Date/Time format.
The DATE_FOR_REPORT_A column is Text format and has been created with this formula: FORMAT(TABLE_A [DATE_A],"YYYY/MM")
Starting from this table, I created a Clustered Column Chart like the one below:
(In Values a MEASURE_A, in Axis the DATE_FOR_REPORT_A)
Similarly, i have another TABLE_B , with columns created like the previous one.
Relatively to this table, i have another Clustered column chart:
(In Values a MEASURE_B in Axis the DATE_FOR_REPORT_B)
I would like to create a Clustered column chart to visualize both values of MEASURE_A and MEASURE_B in the same output.
The problem is that I have no idea on what to put in Axis.
Steps i tried (with failure):
- MEASURE_A and MEASURE_B in Values
DATE_FOR_REPORT_A and/or DATE_FOR_REPORT_B in Axis
the visual output is:
- I created a TABLE_DATE with this formula: TABLE_DATE = CALENDAR(DATE(2019,1,1), TODAY())
This table has a Date (default name) column with Date/Time format.
I also created a column DATE_FOR_REPORT_COMMON in this table (Text format).
I created (inactive) one-to-many relationships:
Date 1---⁎ DATE_A
Date 1---⁎ DATE_B
At the end, I tried to create a Clustered column chart with DATE_FOR_REPORT_COMMON in the Axis and both MEASURE_A and MEASURE_B in Values. Unfortunately not even this solution worked fine:
I really hope someone can help me overcome this issue, because is the last step to end the project I'm working on.
Thanks in advantage,
F.
8 Replies
You're on the right track with a date table. Use a column from the date table on the chart axis.
Where you may be going wrong is with the relationships from the date table to TABLE_A and TABLE_B. You want to create a relationship with the date column to a date column on each TABLE_A and TABLE_B. It looks like you currently have a datetime column and a text column on each of those tables rather than a date column.
In summary, make sure your calendar table has a date column (convert from datetime if needed) and relate it to date (not text or datetime) columns on TABLE_A and TABLE_B.
Hi AlexisOlson,
I'm very thankful for your help.
Unfortunately I'm still facing the same issue, because the output is the same showed in the last picture I posted (both blue and light blue bars have constant values). I don't know what to do...Can you share a sample file (upload and link to a pbix saved in e.g. Google Drive/Dropbox/SharePoint/OneDrive)?
I don't think I can tell why it's going wrong without seeing it.
Hi FabioF ,
The relationships should be:
DATE_FOR_REPORT_A(date format)and Date(date format)
DATE_FOR_REPORT_B(date format)and Date(date format)
Then use Date as the x-axis of the chart.
The thing is will MEASURE_A and MEASURE_B be affected by the x_axis?
You may need to add date condition to your formula, like:
measure = calculate([value],filter(table,condition1&&condition2&&DATE_FOR_REPORT = selectedvalue(date)))
If I misunderstood your meaning, please show some sample data and expected result to us.
Best Regards,
Jay
Hi Anonymous , I renew my thanks for your support.
Here the example files if you missed it in a post above:
Hi guys! @AlexisOlson @v-jayw-msft
Here I replicated a simplified scenario representing the issue I'm facing.
FYI, in the real case I am connected in "Import" mode to an Oracle Database.
.pbix file ---> here
(excel static data set ---> here)
As you can see in the .pbix file, I generated a report that by a slicer should dynamically edit the Stacked column chart. Unfortunately, the view is static and there is an output only when the full date range is selected. If you move the first date even just a day forward, the output totally disappears.
To see the behaviour I'd like to achieve, just drag and drop "START_DATE" in the slicer instead of "Date" (it works...).
The reason I want to use the generic Date in the slicer, is because the next step is put the different measures in the same output (a Clustered column chart, as I explaind in the main post).
The strange fact is that for other reports very similar to the one I uploaded, the output is dynamic (the only difference is that for these other reports in the filed "Values" of the Stacked column chart view there is no Measure, just simple count operations on a column).
I hope you can help me, thanks in advantage!
F.
Thanks so much for your kind support, AlexisOlson and Anonymous
I'll try your suggestions and I'll share asap a simplified data set!
I'll keep in touch!
F.Hi guys! AlexisOlson Anonymous
Here I replicated a simplified scenario representing the issue I'm facing.
FYI, in the real case I am connected in "Import" mode to an Oracle Database.
.pbix file ---> here
(excel static data set ---> here)
As you can see in the .pbix file, I generated a report that by a slicer should dynamically edit the Stacked column chart. Unfortunately, the view is static and there is an output only when the full date range is selected. If you move the first date even just a day forward, the output totally disappears.
To see the behaviour I'd like to achieve, just drag and drop "START_DATE" in the slicer instead of "Date" (it works...).
The reason I want to use the generic Date in the slicer, is because the next step is put the different measures in the same output (a Clustered column chart, as I explaind in the main post).
The strange fact is that for other reports very similar to the one I uploaded, the output is dynamic (the only difference is that for these other reports in the filed "Values" of the Stacked column chart view there is no Measure, just simple count operations on a column).
I hope you can help me, thanks in advantage!
F.
