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.

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