I am trying to create a stacked histogram that counts the number of phases for each month throughout the program duration for filtered and unfiltered data.
Unfortunately, the results are incorrect for unfiltered data
However there is no issue if the data is filtered either at District or project level.
I have attached the powerbi file for your assistance. Appreciate if you also assist in calculating a cum. line curve providing the count summary.
Test4.pbix (2.5 MB)
T/BR
B
Hi @brooks65.
There is a difference in the data assigned to both of your visuals:
My suggestion would be to create 2 tables using the Power BI native visual and ensure the data is correct in each before proceeding further.
As to your specific question, I have no idea why filtered works and unfiltered doesn’t, but highly suspect that the data is the culprit. One thought would be to use an aggragate transform instead of aggregate encoding, and here’s the Vega-Lite documentation for reference:
As an aside, I have a quick comment on the data and scale domain: are you really able to draw insights from a 7-year period 9 years in the future? That seems (to me, at least) way too far ahead to permit actionable business value.
Hope it helps.
Greg
Hi Greg,
Thanks for your prompt response. As advised I have deleted two of the Date fields (Finish & Month-Year) from the lower visual and noticed a change in the spread of values (refer to page 1). But this information was found to be partially true for a specific period alone under no filter condition. The filtered condition works fine.
Just to clarify the first visual is a Gantt chart and so it requires start and finish date fields to display. However, the lower visual is aimed at counting the number of distinct project phases for each period from Jan-33 to 39, I had to have a field that captures the whole period; hence the Date field was applied to lower visual. The Date field lists each day for the entire period but given the display time unit is a month; it has to aggregate the distinct count for each phase per project. I think this is reason for the 10K+ rows.
The entire model works fine when the data is filtered. Secondly when use the built-in power bi stacked column chart the result is correct. So, I do not think there is any problem with the data structure or dataset. Please refer to page 2 of the attachment.
The only reason for me to use Deneb is that the built-in power bi chart the x-axis is dynamic and changes the scale with the filtered data set causing difficulties in the reading the Gantt and bar chart concurrently.
As noted in your response the only option for me now is to create a new table that constitutes of bins for each month for entire duration as a field for x-axis and writing dax measure to count each phase per project for displaying it as y-axis for using the deneb chart to display the results correctly.
Lastly i take your point regarding 7-year period and 9 years in future. This is intentional and the data has been deliberately re-jigged to protect the confidentiality of the project.
Once again thanks for your time.
BR
B
Test4.pbix (2.5 MB)
Hi @brooks65
OK, a couple of things:
Before doing anything else, I’d mark the [Dates] table as a date table, activate the relationship between the [CombTS-S] and [DatesTable] tables, and create a proper dimensional model, say, with [District], [Project], [Phase], and [Dates] dimensions, each with 1-to-many active relationships to the [CombTS-S] fact table.
As mentioned before, then you can create native Power BI tables and measures as necessary to show the desired data. Once you have the tables showing the desired data, you can duplicate the page and convert the duplicated visuals to Deneb visuals and copy the JSON you already have.
Hopefully that will help.
Greg
Hi GM Greg,
Thanks for your advice. It works.
Will be more mindful about the data modelling tips advised.
BR
B
Test4.pbix (2.6 MB)
Hi @brooks65. Glad to hear it was helpful. If the discussions on your issue are complete, please mark the thread as solved to help the forum members. Greg
