Creating a visual combining using columns while maintaining its filterability

[jkevins]

Hi all,

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

username	used personal bike at least once last week to go to work	used personal car at least once last week to go to work	used public bus at least once last week to go to work
name 1	1	0	1
name 2	0	1	1
.......	1	0	1

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!

[v-shex-msft]

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

[lbendlin]

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"

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

[jkevins]

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)?

[lbendlin]

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]

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?

[lbendlin]

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

What made you choose bidirectional?

[jkevins]

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

[lbendlin]

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

[jkevins]

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

[lbendlin]

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