Help with Creating a Simple Chart

[TD21]

I'm having issue creating a very simple chart and would appreciate some help.

My data looks like this:

Order #    Region    State    Apple   Banana   Orange   Grape   Pineapple

A-001      South     LA        x        x        x        x      x

A-002      East      NY        x                 x        x

I would like to create a bar chart like the one below, with Region and State as slicers. And I can't seem to create this simple chart.   Any help is greatly appreciated.

Sample file: https://drive.google.com/file/d/1aFz-bNYsWiNQ4yFhN6N6Uc7rqiOEqBFH/view?usp=sharing 

[jdbuchanan71 · Accepted solution]

In the query editor, select the order, region, and state columns the go to transform > unpivot > unpivot other columns:

Then select the Value column and remove blanks, leave only the X's

Rename Attribute to Fruit and click close and apply.

Then you can add the fruit column to the axis and a simple measure to count the rows of the table to get your count by fruit.

I have attached my sample .pbix for you to look at.

[TD21]

Worked beautifully in Import mode. Thank you!

Out of curiosity, is there a similar function if using Live Connection to a SSAS tabular cube?

[jdbuchanan71]

You don't have the transform options when connecting to a SSAS model but you could transform the data before feeding it into SSAS using a UNION in a SQL view that would replicate the unpivot.

[TD21]

I'll give UNION a try. At least now I have a back-up plan if that doesn't work out.

Thank you again. Appreciated.