multiple dates on axis

[sandersonmm]

Hi,

I have 2 dates that I want to plot on the X-axis and a count of case IDs on the Y-axis. I cant seem to be able to add in more than one one dimension onto the X-axis. 

[Mayank_Yavda]

Hi @sandersonmm,

Here, is Line chart with dual x axis which will help you to get secondary x axis, make sure both the dates column is in same table or else they must have a defined relationship( if from different tables ). Hope this helps you.

Line chart with dual x axis PBIVizEdit.comLine chart with dual x axis PBIVizEdit.com

Download link for the custom visual file in this page,

https://pbivizedit.com/gallery/dual-xaxis-line-chart 

Youtube tutorial,
https://www.youtube.com/watch?v=_yJURZ-KOkY 

This was made with our Custom Visual creator tool PBIVizEdit.com. With this tool,

• anyone, irrespective of technical skills, can create their own visuals
• 15 minutes to create a visual from scratch
• opens up many additional attributes to edit (for e.g. labels, tooltips, legends position, etc)

Give this a shot and let us know if you face any problem/errors.
You can use the editor to modify your visual further (some modifications cannot be done in Power BI window and have to be in editor).

Thanks,
Team PBIVizEdit

[sandersonmm]

This is what I would like to achieve, a line for create date and another line for close date. They would both be counting the case id. The idea behind it would to display when the case was open and closed. Maybe there is a better way of displaying it?

 

[jsaunders_zero9]

Using my sample below the 16/04/2022 should show a line for 2 cases opened and a 1 case closed to satisfy your requirement?

[sandersonmm]

I would like to see just months on the x axis. So, 1,2,3,4... 12. Then it would count the number of case id on the Y axis.  Based on you example the create date line would have a count of 20 for the month of April and the close date line would have a count of 17 for April and a count of 3 for May.

[jsaunders_zero9]

Like this? CasesByMonth_Example.pbix

[josejo9423]

Hello the link has been disabled, could you reshare it please?

[sandersonmm]

Yes, that is perfect. However im working on an older version of power bi and cannot open that. Can you send me screen shots of what you did?

[jsaunders_zero9]

Good to hear,

TABLES

_Calendar[Date] joins to Created Date and Closed Date

MEASURES

CasesOpened = CALCULATE(COUNTROWS('Cases'),USERELATIONSHIP('Cases'[Created Date],_Calendar[Date]))
CasesClosed = CALCULATE(COUNTROWS('Cases'),USERELATIONSHIP('Cases'[Closed Date],_Calendar[Date]))

VISUALS

[ShyamRotrix]

I am facing similar issues. When I tried to make relationship it allows only one relationship to be active. If I make created date as active, the no.of cases created at that month is showing correct, but when I add closed. It is only showing the created values in closed. Same when I make closed date as active relationship. When I try to activate both the relationship it is throwing error.

[sandersonmm]

Hello,

can you share your data souces and relationships? what you posted doesn't work for me. 

My Date table, date formated as date

case table, create date and close date are formatted as date

My relationships wont work, when i try to join them

Also i dont know why my x axis wont show the date on the x axis as well

[jsaunders_zero9]

@sandersonmm 

This error is telling you that there is a relationship from another table that will affect the new relationships you have created.

If you can send me a screen shot of the entire data model relationship view we can try and work it out. Alternatively the measures that I have provided will work even if the relationships are set as inactive (dotted line).

[sandersonmm]

Hello,

Attached is the entire data model

[jsaunders_zero9]

There is a bit going on here, Looking at your model you can set the relationships that I gave you as inactive and the measures will still work as they are calling the USERELATIONSHIP() function.

[sandersonmm]

Hi saunders,

I turned off the relationships and it seems to be working a little better, previously it wouldnt show any of the dates on the bottom. I also wanted to ask if we are using the same visualizations chart as well. 

[jsaunders_zero9]

What is this?

The measures that I gave you should be here.

[sandersonmm]

I tried doing what you did but it would give me this error (after I turned off the relationship ) So i just switched it to count of close/create dates

what it would give me if i turned on the relationship

[jsaunders_zero9]

When you say "turned off" the relationship what do you mean? have you removed the relationship (no line) or made it inactive (dotted line)?

Yes these are the same visual.

[sandersonmm]

@jsaunders_zero9 

After looking at my data  I know what my issue is. 'Case id' on my 'cases' table can be duplicated. Is there a way to have the distinct count of a case id for both 'close date' and 'create date' based on the calendar date? I want to do it this way so I have the calendar date as a single filter for both of it.

[sandersonmm]

Yes i have turned it off.

[jsaunders_zero9]

Could you talk more about what you are trying to demonstrate by doing this? Sounds like it would look confusing, maybe there is another way to acheive what you need.