cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AoJueMing
Helper I
Helper I

Show items with no data and slicers

Hi

 

I have made a new thread because there seems to be various problems with this function but none that match the one I am having.

 

So, for example, I have this data set:fvyhrrt.PNG

 

I create a column chart and a slicer like so:ghnfuhnygtr.PNG

 

 

 

I tick the "Show items with no data" option for the column chart:hwytjxsr.png

 

 

 

Now when I slice by, for example, "A", the result is this:kjhgfds.PNG

 

 

 

As you can see, there are no entries for 'Place A' with a 'Score' of 3/6/10, so they have been removed from the axis.  I do not want them removed from the axis, which was the rational for ticking "Show items with no data" on the column chart.  I want the axis to remain static regardless of whether there is a count for the individual axis columns.

 

What am I missing?

 

 

Thanks guys

 

 

 

 

 

 

 

 

 

8 REPLIES 8
parry2k
Super User
Super User

I believe in this case you have to have another table with "Id" and set relationship with your data table and put "Id" from your new table on "Axis"

 

 






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.





also you have to select "Type" as Categorical on "X" Axis, here are some screen shot for your reference:

 

no data.PNG

no data 1.PNG

no data 2.PNG






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.





OK parry2k, thanks for your help.  I may need a little more clarification.

 

So I have now made a seperate table with just ID - and set the 1:1 relationship with the other data table:jytjhnuy.PNG

 

bnsyrj.PNG

 

 The first problem I have is when I put "ID" from the new table on the Axis:hrjynx.PNG

 I fail to understand how this could possibly generate the same chart?  (Even with catagorical selected as 'type' in the X-Axis):kydhrgf.PNG

 

 Have I misunderstood something already?

 

 

 

 

 

 

You are correct, I misread some requirement here,  I did put together a pbix file which I can send it to you, shoot me your email id in private message.

 

Thanks,

P






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.





radhi
Regular Visitor

Hello Der,

I am also facing similar issue in my report,  could you please share the file with me if possible.

 

Thanks,

Radhi

Hi Radhi

 

I'm afraid I don't have the file anymore.

 

As far as I can remember, I ultimately resolved this issue by creating a custom column in the data set and using the 'sort by column' fuction under the 'modelling' tab - this gave me more freedom with the axis labelling/order - but I'm afraid I cannot show you or remember the exact details.

 

Hope this helps.

 

 

radhi
Regular Visitor

I am facing the similar issue in my report, could you please share me the file wherein you have sorted this issue.

 

Thanks,

Radhi

Hi Parry

 

I've looked at the file and had a play around, and I understand what you've done.  You did, technically, fix the problem, so thanks for that - though my real issue was slightly shoruded.  I kept getting the same results even after applying your method, which looked the like:

nsytjhnju.PNG

 

The reason for this was that I had the 'Score' column modelled as text.  And here's why my problem was shrouded somewhat - 

The colum HAD to be formatted as text, because that is the only way I could list that row as 01, 02, 03, 04 etc, instead of what it would automatically be changed to if I formatted as a number column (1, 2, 3, 4, etc).  The reason I needed them to be 01, 02, 03, 04 etc is because these values are actually supposed to represent months.  When I make a chart, I cannot move columns around as I please, so I need to build into the data the pillars necessary so that I can later 'Sort by score', which will visually allow me to show Jan, Feb Mar, Apr as 01, 02, 03, 04... etc.  This is only a problem because of the limited functionality when organising colums in charts.

 

 

I received a dataset with January representaed as Jan, February represented as Feb, etc.  In order to chart this out, I created a colum which made Jan=01, Feb=02, Mar=03, etc.  The idea is I would then use these values as the axis on a bar chart, counting instances of the months:geathgsrt.PNG

 

As you can see, the idea was to use a slicer so that the effect on the counts of the values in the differing months could be explored.  The reason they have to be represented as 01, 02, 03 - is because it represents a year cycle, and changing the 'Score' colum to number would make that impossible.

 

Anyway, that was just a little explanation of why I needed what I needed, and why your solution did work - but not under the strict circumstances I was trying to make it work.

 

Thanks for your help, much appreciated.

 

 

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!