Modeling for a relational column with line chart

[TrentS]

Greetings all,

I need some suggestions on how to model a column w/line chart showing case status over given time periods (I just used months in the examples but have week and quarter breakouts in the data).

Tracking how many New, Closed, and Open (the line) cases occur in the period. Using the time intelligence functions I can get week/month/quarter designations from the case date stamp and can chart it in a separate table, however it is not relatable to the core case table. The function to calculate Open cases stems from an earlier post I made (Calculate(Count(),Filter)) and received help on.

One suggestion was to make additional rows for each case separating Open and Close dates, assign values (1/-1), and count those, however I would like to avoid duplicating the data size.

I have some functions in place to fill in a future "Close" date so as not to have blanks in the actual data. Then just leave that future date out of the charting.

Making the Chart(Month) relate to Case(Month_Open or Month_Close) doesn't work.

Suggestions on how to structure that to make the Column/Line chart relational to the primary case table? How would you accomplish it?

I created some sample data for reference.

ChartingCaseTablesTrent

Edit: Guess I should indicate that I want it relational so that if I filter by the Case Owner (or other columns not shown in the Case Table sample) that the chart filters appropriately with it.

[TrentS]

This was solved by creating a date table instead of the columns within the Data table itself. Sam from Enterprise DNA posted a blog about handling multiple dates within data tables that help me model it. Some other DAX work around that additional logic (and making the date table inactive relationships per Sam) allowed me to get the dynamic charting I was looking to achieve.

I recommend checking out Sam's blog and videos about it.

https://community.powerbi.com/t5/Community-Blog/How-To-Deal-With-Multiple-Dates-In-Power-BI/bc-p/758...

Thank you to those offering me suggestions on this endeavor!

TrentS

[TeigeGao]

Hi @TrentS ,

Could you please share your expected result to us?

Best Regards,

Teige

[TrentS]

The result is a graph that looks like this but must be responsive to filters from the primary case table.

If there is a way to include those counts, perhaps via DAX measures or columns instead of a separate table and worrying about a relational issue, I am all for it!

The numbers on the Charting table itself come from DAX Calculate(Count(),Filter()) functions based on the data in the primary case table.