cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

Charting.PNGChartingCase.PNGCaseTables.PNGTablesTrent

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.

3 REPLIES 3
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,

Teige

TrentS Regular Visitor
Regular Visitor

Re: Modeling for a relational column with line chart

Chartexample.PNG

 

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.

 

Highlighted
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.

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

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)