cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jkevins
Frequent Visitor

Creating a visual combining using columns while maintaining its filterability

Hi all,

 

I have a dataset with 4 columns consists of information as follows:

usernameused personal bike at least once last week to go to workused personal car at least once last week to go to workused public bus at least once last week to go to work
name 1101
name 2011
.......101

 

I'd like to build a bar chart which bars consist of the percentage of people who used personal bike, personal car, and public bus last week to work. I tried to use clustered bar chart and put the three aforementioned columns into the values and aggregate them as average - but got proportions instead because formatting as percentage is not available. Plus, that aggregated clusered bar chart cannot be used to filter other chart like usual Power BI graph. Any idea on this?

 

Thank you so much!

10 REPLIES 10
v-shex-msft
Community Support
Community Support

Hi @jkevins,

Can you please share some dummy data keep raw data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

The first step in such a situation is to unpivot the data to make it usable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykvMTVUwVNJRAmEDMB2rAxU2ggvBhPUgAFV5LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [username = _t, #"used personal bike at least once last week to go to work" = _t, #"used personal car at least once last week to go to work" = _t, #"used public bus at least once last week to go to work" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"username"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

 

lbendlin_0-1630715811559.png

 

Once you have that format you can feed your visuals as desired.

 

jkevins
Frequent Visitor

I see. Assuming that other fields would have unique username column and that this queery wouldn't, then the figure created using this query wouldn't be able to filter other figures (because its not one to one)?

Power BI likes to work with data models. Fact tables (things you can compute) and dimension tables (things to filter/group by). Think about the data you have and how it would fit into such a data model.

jkevins
Frequent Visitor

Hello,

 

Thank you for your response!

 

I'm a little stuck on connecting the two. My fact tables which contains the information as illustrated in the image on your previous post, has duplicates of keys (or in this case usernames) due reshaping from wide to long, whereas the dimension tables have all unique username. I can connect them bi-directionally using many to one relationship and that other graph/slicer can be used to filter this data but not the other way around. I'd like to also be able to filter data using this aggregate graph. Any thoughts on this?

Normally you want to avoid bidirectional search. Filters should ideally only flow from the dimensions to the facts.

 

What made you choose bidirectional?

jkevins
Frequent Visitor

Because in the example above, I'd like to filter people based on their transportion of choice (e.g., among people who use cars, how many of them are x, y, z; same with bike and bus. The current relationship work only 1 direction (one-to-many). 

 

However, the long table does have duplicates of keys and are aggregated in the graph - that's why it is so confusing. Obviously, the choice is to separate them into each of their individual graph, but I'd prefer to have them all nicely in one graph

Are the duplicates meaningful or could you solve this by adding a deduplication step in Power Query?

jkevins
Frequent Visitor

Yes it is meaningful. The duplicates are basically different types of transportation each username use in a long format (attribute - value columns)

Maybe you can provide a larger sample of the source data, and describe the desired outcome(s) for your scenario(s) ?

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.