Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alvin199
Helper III
Helper III

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.

 

Table.png

1 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): 

 

AllisonKennedy_0-1646693136980.png

 

Hopefully that all makes sense?

 

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

4 REPLIES 4
AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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

@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): 

 

AllisonKennedy_0-1646693136980.png

 

Hopefully that all makes sense?

 

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.