cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Probably an easy count Q

Hi guys,

 

If I spend over an hour trying to figure something out, I just post it here for the pros to figure out!

 

This seems relatively simple, and I have done this before on a different PBIX file but I can't recreate it even though I can reference the working file...

 

I am trying to create a stacked column chart that shows the number of tickets opened and closed by day. It should look like this:

OpenVsClosed.png

I have two tables: One of them is my date table Date, and the other is my source data table Service.

 

There is a field in my date table called date, which is data type date/time.

There are two relationships from the date table to the  Service table:

  • 'date'[DateAsDate] -> 'Service'[date_closed]      (active relationship)
  • 'date'[DateAsDate] -> 'Service'[date_entered]    (inactive)

I created a couple of calculated columns in the Service table following the protocol that I used previously with success

  • Column Open = calculate(count(Service[SR_Service_RecID]),Service[date_entered])

  • Column Closed= calculate(count(Service[SR_Service_RecID]),Service[date_closed])

Then I plugged them into a stacked column chart like shown above but the chart remains blank!

 

Any ideas?

 

THANK YOU in advance 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Probably an easy count Q

Hi @Shpongle ,

 

It was showing as Day no, because the earlier selection in the x Axis was Day (under the heirarchy of Date).

 

Have changed the x axis values .Pulled the Date from the Calendar table and removed the hierarchy.

 

1.jpg

 

 

 

Incase you want for each date, you can change it the values of X axis to Categorical.

 

2.JPG

 

 

Hope this helps.

 

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 

View solution in original post

12 REPLIES 12
Highlighted
Super User IV
Super User IV

Re: Probably an easy count Q

@Shpongle add measure like this, not columns

 

Measure Open = count(Service[SR_Service_RecID])

Measure Closed= calculate([Measure Open],Userelationship(Datetable[date], Service[date_closed]))

 

and use the above measures in the visual

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper III
Helper III

Re: Probably an easy count Q

Thanks, @parry2k - I agree that seems like it *should* work, but I got similar results: No data shows up and I observe that the left axis shows percentage!

CurrentResults.png

I figure I'll share all the details in case I screwed something up. 

 

Here are the tables and relationships:

Relationships2.pngRelationships1.png

 

Following is how I set up the measures:

Measures.png

Highlighted
Super User IV
Super User IV

Re: Probably an easy count Q

@Shpongle change date_entered data type to date instead of date/time and that's the issue.

 

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper III
Helper III

Re: Probably an easy count Q

Working on the weekend! Thanks for getting back to me today, @parry2k - very cool.

 

Welllp, still no good results. I checked and they (date_opened / date_closed) were both date/time, so I changed them both to date.

 

The one thing that I find odd is that there is no icon to the left of the date_closed field - see the last item in the image below...

 

Check it out:

 

StillBuggin!.png

 

Highlighted
Super User IV
Super User IV

Re: Probably an easy count Q

@Shpongle that's fine because you had the relationship on that column, not sure why it is not working, I would recommend to share pbix file with sample data and remove any sensitive information before sharing.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper III
Helper III

Re: Probably an easy count Q

Hi - I have probably spent five more hours trying to self service on this one... just... because... I thought I could figure it out!!!

 

A quick refresh: I'm just trying to create a stacked column chart that shows the number of tickets opened vs the number of tickets closed by day. The only visual that I have set up on this shared pbix is trying to accomplish this but it shows a percentage instead of a count on the left axis, and it doesn't show any ticket counts!

 

The following is close enough to what I want to serve as an example - if we can get it here then I can take it the rest of the way:

OpenVsClosed.png

Here is what I get:

What I'm getting now.png

 

THANK YOU for offering to help out, @parry2k (or anybody else who has a suggestion!)

Highlighted
Helper III
Helper III

Re: Probably an easy count Q

Hi all, I spent a little more time on this over the weekend but I'm stumped! I'm wondering if the .pbix file has been compromised! I genuinely cannot figure out why I can't create this simple view.

 

Link to the pbix in the post above if anybody wants to take a shot at it.

 

Thanks

Highlighted
Helper III
Helper III

Re: Probably an easy count Q

Hi - I'm starting to think this thread got a little too long for anyone to take an interest! If you read only the post that is two posts above this one, it will boil it down, and it has a link to the file.

 

I'm hoping someone can help out - I'm feeling pretty burt out on this one but need to make it work

 

Thanks!

Highlighted
Community Champion
Community Champion

Re: Probably an easy count Q

Hi @Shpongle ,

 

I think there is some issue with the model or file. 

I copied your table in an excel sheet and imported it again and i am getting the values.

 

1.jpg

 

 

 

 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 

 

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors