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.

 

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 171 members 2,389 guests
Please welcome our newest community members: