Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
Long time watcher, first time poster. I am a teacher who is beginning learning how to use PowerBI. I wish to be able to display my student results in a more aesthetically pleasing way (hence, learning PowerBI).
I have included a dummy file of where I am up to below.
I am wanting to be able to select a students name and have their result highlight in the Matrix (not filter/slice other data out of the matrix). I know I can do this in Excel with a conditonal format lookup (I have done it there before), but am wondering if it is possible to do it in PowerBI.
Here is what I want:
I have included my data below:
https://1drv.ms/u/s!ApsxXn-MGjNCftks6yoGbIjR_Rs?e=lkJ4Oy (PowerBi file)
https://1drv.ms/x/s!ApsxXn-MGjNCf_VHgWvvZigiPYI?e=no7AC4 (Excel source file)
Thanks in advance to anyone reads this far down, and even larger thanks to anyone who has ideas/solution to this.
Paul
Solved! Go to Solution.
Here's an alternative approach that enables you to avoid creating a copy of your table.
Create measures:
Result Count = COUNT ( 'Yr7 data'[Result] )
Result Count All = CALCULATE ( [Result Count], ALL ( 'Yr7 data'[Student Name] ) )
Result Count Student =
VAR vStudent =
ALLSELECTED ( 'Yr7 data'[Student Name] )
VAR vStudentResultCount =
CALCULATE ( [Result Count], 'Yr7 data'[Student Name] = vStudent )
VAR vResult =
IF ( NOT ISBLANK ( vStudentResultCount ), "#f4f142" )
RETURN
vResult
Note: you can change the color by changing "#f4f142" to the desired color.
In the matrix, the Values should be [Result Count All].
Set conditional formatting in [Result Count All]:
Result:
Proud to be a Super User!
Hi @Dhuey ,
Here is a little modification of DataInsights's reply when you try to multi-select student name in slicer or with no selection in slicer.
You can add the following measure in your background color based on field:
Measure =
VAR a =
CALCULATE (
COUNT ( 'Yr7 data'[Result] ),
'Yr7 data'[Student Name] IN VALUES ( 'Yr7 data'[Student Name] )
)
RETURN
IF (
ISFILTERED ( 'Yr7 data'[Student Name] ),
IF ( ISBLANK ( a ), BLANK (), "Yellow" ),
BLANK ()
)
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXGMeGpHdrlBmsFV2D...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@AllisonKennedy @DataInsights @v-deddai1-msft
Thank you all so much for your insights and help. I can't wait to test this with real data across all year levels, will certainly help out at parent conferences.
Dhuey
Hi @Dhuey ,
Here is a little modification of DataInsights's reply when you try to multi-select student name in slicer or with no selection in slicer.
You can add the following measure in your background color based on field:
Measure =
VAR a =
CALCULATE (
COUNT ( 'Yr7 data'[Result] ),
'Yr7 data'[Student Name] IN VALUES ( 'Yr7 data'[Student Name] )
)
RETURN
IF (
ISFILTERED ( 'Yr7 data'[Student Name] ),
IF ( ISBLANK ( a ), BLANK (), "Yellow" ),
BLANK ()
)
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXGMeGpHdrlBmsFV2D...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@DataInsights Yes, that is a better solution, I just went with the fact that duplicate table was already created, but much more efficient to just use a duplicate measure.
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
Here's an alternative approach that enables you to avoid creating a copy of your table.
Create measures:
Result Count = COUNT ( 'Yr7 data'[Result] )
Result Count All = CALCULATE ( [Result Count], ALL ( 'Yr7 data'[Student Name] ) )
Result Count Student =
VAR vStudent =
ALLSELECTED ( 'Yr7 data'[Student Name] )
VAR vStudentResultCount =
CALCULATE ( [Result Count], 'Yr7 data'[Student Name] = vStudent )
VAR vResult =
IF ( NOT ISBLANK ( vStudentResultCount ), "#f4f142" )
RETURN
vResult
Note: you can change the color by changing "#f4f142" to the desired color.
In the matrix, the Values should be [Result Count All].
Set conditional formatting in [Result Count All]:
Result:
Proud to be a Super User!
@Dhuey This was a fun example. See the attached file under my signature. I created a measure to calculate the student results, using your copy unrelated table:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |