Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Jonwig
Frequent Visitor

Showing Multiple Measure in one bar chart cleanly

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

 IDAware Brand1Aware Brand2 Aware Brand3 Aware None 
1Brand1Brand2  
2Brand1   
3 Brand2  
4 Brand2  
5   None
6  Brand3 
7Brand1Brand2Brand3 
8 Brand2  
9 Brand2  
10Brand1Brand2  
11Brand1Brand2  

 

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.

 

1 ACCEPTED SOLUTION
robstewart89
Advocate IV
Advocate IV

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

View solution in original post

6 REPLIES 6
robstewart89
Advocate IV
Advocate IV

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

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?

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!

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.

 

 

 

 

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.