Methods for a static x-axis

[AoJueMing]

Hi guys

I have been wrestling with this problem for a while on and off.  I have solved it previously with battlefield dressings which worked for as long as they needed to at the time but I'd really just like to get a solid method sorted out as it comes up often enough.

I want the x-axis to be static, irrespective of the data been read by the visualisation.

I've mocked up an example:

I have asked 50 people how many of each fruit they have (apples, oranges, pears, bananas). The answers range from 1 - 4 for each fruit.  My relationships look like this:

Here's a quick display of what each query looks like:

And here's the report tab with the visualisations for each question/data set:

So you can see the issue here.  What I want is for every chart to show the x-axis values of 1,2,3,4, irrespective of whether these values are represented in each individual query.  

The idea I have been working with recently is to have a seperate and disconnected table (query) with the x-axis values(a simple 1x4 table with the values 1,2,3,4), and using that as a standard when constructing each visualisation - but I haven't managed to make it work yet and as I was struggling with that for a number of hours I thought I might as well make a post on the forums and collect ideas for how other people would go about such an issue.  

Any and all discussion on methods for this type of issue will be well received (including anyone who thinks the data moddelling is awful - if so please do let me know how you would start again).

Thanks.

[jthomson]

Have you tried "show items with no data" using the separate 1-4 table you've been playing with?

[AoJueMing]

I have not managed to create a functional visualisation using the seperate table and data from one of the queries yet, so no.

Any suggestions on how to do that would be appreaciated.

[jthomson · Accepted solution]

---

@AoJueMingwrote:

I have not managed to create a functional visualisation using the seperate table and data from one of the queries yet, so no.

Any suggestions on how to do that would be appreaciated.

---

I've just noticed that your 1-4 table is disconnected per your OP, connect it to each of your tables and put it as your x-axis as a starting point

[AoJueMing]

OK, well, thanks for sorting out my issue.  It was really useful to just go through stage by stage, sometimes you can't see the wood for the trees.  Much appreciated.

[AoJueMing]

The problem with this, was that I ran into this:

(Moon being the 1x4 'satellite' table of 1-4 as the answer peramaters).  I can only have one active relationship here.  If I try to make active another relationship i get this:

(Which I don't understand).

So now I can get the effect I want with the 'Pears' query:

But I cannot activate the link between Moon and any of the other queries.  Attempting to make the visualisations for any of the other queries simply gives the correct values for the 'Pears' query (as that is the query with the active relationship to Moon):

So now what I need to do is;

Find a way that I can make active the connections between Moon and Oraanges/Bananas/Apples, or

Accept that this data moddelling method is not how PowerBI wants to play and find something else.

Do I need to have a seperate Moon for each and every Question data set/query?  Or can I use the same Moon for multiple questions/data sets?

And of course, as I said previously - Is there a better way to do this altogether?  Is there any benefit to forcing all this data into a single query - or is building a gigantic web of relationships between multiple simple datasets/queries a legitimate method?