Showing results for 
Search instead for 
Did you mean: 
TrentS Regular Visitor
Regular Visitor

Modeling for a relational column with line chart


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.


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.

Community Support Team
Community Support Team

Re: Modeling for a relational column with line chart

Hi @TrentS ,

Could you please share your expected result to us?

Best Regards,


TrentS Regular Visitor
Regular Visitor

Re: Modeling for a relational column with line chart



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.


TrentS Regular Visitor
Regular Visitor

Re: Modeling for a relational column with line chart

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.

Thank you to those offering me suggestions on this endeavor!


Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 330 members 3,490 guests
Please welcome our newest community members: