Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shpongle
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

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
parry2k
Super User
Super User

@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!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

@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!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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!)

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

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!

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)

 

 

 

Hi Harsh,

 

I appreciate the effort here - The only problem is that the chart is showing the total number of tickets as opened and closed for each day! The other weird thing is that it's not showing the date along the bottom, just a day number.

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)

 

AT LONG LAST!!! It's working! I feel like a goof for not being able to figure it out and I'm still going to need a minute to review why it's working but.... IT WORKS!

 

I'm not sure why my date table didn't work but yours did. I think my date table might need a little review. Once I switched it over to your calendar, *boom*. 

 

I spent an embarrasing amount of time on this, and so I am very grateful to you, @harshnathani, for taking the time to help!

 

Kind Regards,

 

Mitch

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.