Showing Multiple Measure in one bar chart cleanly

[Jonwig]

Hi PowerBIers,

I have what I think should be a simple question but cant seem to find the answer to it anywhere.

I have market research data, where I have the awareness for a number of brands as different measures. 

Extract of Raw data like this

ID	Aware Brand1	Aware Brand2	Aware Brand3	Aware None
1	Brand1	Brand2
2	Brand1
3		Brand2
4		Brand2
5				None
6			Brand3
7	Brand1	Brand2	Brand3
8		Brand2
9		Brand2
10	Brand1	Brand2
11	Brand1	Brand2

I make measures in Powerbi to derive the % aware for each brand

Aware Brand1 % = count(Data[Aware Brand1])/COUNT(Data[ID])*100

Aware Brand12% = count(Data[Aware Brand2])/COUNT(Data[ID])*100

When I want to chart this as a bar chart is is looks pretty bad in PowerBI. Eg I put each indivudal created measure into the value field with no Axis or legend data field. There is no way to get X axis labels, the bars are all squashed together. 

Is there a better way of doing this that I am missings so I can use more of the Charting power of PowerBI with data like this?

Thanks for the help.

[robstewart89 · Accepted solution]

Hi ,

Bit of a workaround but you could create a new table (by using Enter Data) with the name of your measures:

Table Name: Aware
Aware Headers:
Aware Brand1
Aware Brand2
Aware Brand3
Aware None

Then create a switch statement like the below:

SWITCH (
    MIN ( 'Aware'[Aware Headers] ),
    "Aware Brand1", [Aware Brand1],
    "Aware Brand2", [Aware Brand2],
    "Aware Brand3", [Aware Brand3],
    "Aware None", [Aware None]
)

Use the switch statement above as your value and use your Column Headers from the table as your X axis

[Anonymous]

This probably won't be replied to, but I'll try. I saw this looking or a similar problem I have, and this worked as it created the bars separated with the X axis labels, howver, the filters I created (month and date), don't modify these bars. How can I do it so the "switch" column varies when varying the filters?

[adrianmazzarolo]

Hi there, I have been having this same issue and wondering how the MIN('Aware'[Aware Headers]) statement works. Is this taking the minimum of all the fields? I attempted this, but it didn't work as the MIN() statement only allows 2 arguments. I have searched everywhere, and this solution seems to be one of the few that works, so please help as soon as you can!

[Jonwig]

As far as I am aware the Min fuction is ensuring only one value is passed through to the Switch function when it is searching through each of the values. Docs.Microsoft sums it up nicely. For switch you need to have - "Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context)."

In the mean time I have developed what I think is a better approach to this issue. (I am talking about research data here, so row level per respondent with these multi-choice questions)  I create a duplicate of my main data table. just with Respondent ID, and the multi choice questions. I then use unpivot to give me a flat table. Which looks like this

RespondentID        Value

1                            Aware Brand 1

1                           Aware Brand 2

2                           Aware Brand 1

3                          Aware brand 2

3                          Aware brand 3

I then link this back to the main table via respondent ID  and you can easily build charts from this. You can even add a third column to this being question number and then have all of your multiple choice questions within one table.

This is much more efficent from a data model persepctive as I found the Switch function was killing performance when I had alot of them in the tables with lots of brands.

Hope that helps.

[LucyCC]

Thank you so much for taking the time to come back and report on this - I was struggling with something similar and you helped!

[Jonwig]

Perfect thank you. It worked just as you described.

(It is a shame you have to use work arounds for this data setup. For market research it is quite a standard.)

Jon