Forum Discussion
Inquiry about Suitable Visual
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.
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?
4 Replies
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)
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
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?
