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

Methods for a static x-axis

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:ex1.PNG

 

 

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

 

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

 

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.

1 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

View solution in original post

5 REPLIES 5
jthomson
Solution Sage
Solution Sage

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

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.

 

 


@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

View solution in original post

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.

The problem with this, was that I ran into this:ex4.PNG

(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:ex5.PNG

 

(Which I don't understand).

 

 

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

 

 

 

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):ex7.PNG

 

 

 

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?

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!