Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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:
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 🙂
Solved! Go to 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.
Incase you want for each date, you can change it the values of X axis to Categorical.
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)
@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!
I figure I'll share all the details in case I screwed something up.
Here are the tables and relationships:
Following is how I set up the measures:
@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:
@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:
Here is what I get:
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.
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.
Incase you want for each date, you can change it the values of X axis to Categorical.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |