Inquiry about Suitable Visual

[alvin199]

Hi, what will be the suitable visual that can use the table below in Power BI Desktop? The 1 means the student taking that subject while - means not talking.

[AllisonKennedy]

@alvin199 

This looks like a Matrix visual. 

Assuming you have a table with columns:

* Name

* Subject

Put Name in ROWS

Put Subject in COLUMNS

Create a new measure:

Count Taking Subject = COUNT(Table[Name]) 

and put that new measure in the VALUES

If you want it to display 1 and you have duplicates in your dataset let us know what your source data looks like so we can help better (paste table data directly here, not just screenshot please)

[alvin199]

Hi @AllisonKennedy 

I have followed your advise but unable to produce a desire matrix table.

If the visual must be besides table form, what is the suggestable visual that possible handle multiple value in a row?

I have attached my PBI file for your advise on what wrong on my matrix table:

https://drive.google.com/file/d/12SuMhRbVR7jp3cFMod0xTdxLbUnoaNqu/view?usp=sharing

[AllisonKennedy · Accepted solution]

@alvin199  Thanks for the sample data - I misunderstood your post and thought you were giving the desired result when you were actually giving the source data. 

Power BI works best with a nice database structure, so your source data needs to be unpivoted. I have done this in the sample file attached below signature. You can do this in Power Query. Click the Student column > Transform tab > Unpivot other columns. Rename Attribute to Course.

Then you can follow my instructions much easier without the need to create a separate measure for each subject. 

If you don't unpivot the columns, your measures can work but it's much more manual. You need to use SUM instead of COUNT. I've done this for the No. of Art measure for you only (because the Unpivot is better way to go): 

Hopefully that all makes sense?

[alvin199]

That is what I expect.

I am using the Transpose method and find it unable to cater my request. Eventually, Unpivot did the job well. 

In short, Power BI expects all the data with the same kind store in a single column. 

Thanks for the advise.