Q&A multiple data side by side without "by" relation

[chrisb0w]

Hello,

in my dataset is data for a histogram that i called simply h1, h2, ... h10. In my case it represents distribution of lenghts of objects in 10 different lenght intervals (h1-h10). So the value of e.g. h1 is the amount of objects that fit this lenght interval.

It is easy to create a Report with this data as a vertical bar chart that shows 10 bars (for h1-h10) side by side.

My data comes continiously from Azure Stream Analytics and the bars in the Report grow fast. So I want to use PowerBI Q&A to set a Time-Window of lets say 1 hour.

I don't know how to ask questions to a PowerBI Report directly so I tried to build the histogram with my Q&A query. The Problem is:

• If I say "h1 as column last 1 hour" it's the result i want for just the single h1 value
• But as soon as I try to add the remaining data it tries to do a "by" relation. So e.g. I say "h1, h2 as column last 1 hour" the result is not 2 bars (for h1 and h2) side by side, but it tries to display "h1 by h2"
• Or e.g. "h1, h2, h3 as column" are not 3 bars like I want but Q&A gives me "h1 by h2 and h3"

I don't get it how I can achieve with Q&A something like "h1,h2,h3,h4,h5,h6,h7,h8,h9,h10 as column last 1 hour" and the result should be a chart with 10 vertical bars for h1-h10 side by side, with the data of the last hour.

I tried a lot of things but can't get my result 😞

[mike_honey]

I'm not sure I understand the shape of your data - can you provide more info? 

Do you have 10 columns h1 - h10?  If so, you probably need to Unpivot your data from that shape into a pair of columns - "Attribute" (e.g. h1, h2) and "Value" (your count values). Each input/current row would be represented by up to 10 rows with Attribute/Value pairs.

That should then be a better shape to chart your data in Power BI.

To achieve, this you would need to manipulate your data in Power BI Desktop (when Editing a Query, Unpivot is on the Transform ribbon) or some other data transformation tool upstream.

[chrisb0w]

Here is the report what I'm talking about.

You see my dataset "filamentdata" contains felds h1-h10 and when I select them all I can display them as vertical bars. It is not nice that there is no value on the x-Axis. If I could choose there have to stay 10, 20, ... 100 because that are the length intervals in my application  and h1-h10 are number of objects.

You see the amounts (y-axis) are very large now because I had the Azure Stream Analytics running a while that is the input. So my plan now is to window over a time and count always the values in that time window for the chart.

But my attempt to do this (just for h1 and h2 for example) looks like this

I expect 2 bars (h1 and h2) but get this h1 by h2 chart.

[mike_honey · Accepted solution]

Your data is in the shape I expected. I would Unpivot it as I described above.  I would also rename / replace all the h1-h9 values to h01-h09, to fix the obvious sorting issues.