Highlighting slicer results in matrix help

[Dhuey]

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

[DataInsights · Accepted solution]

@Dhuey,

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:

[Dhuey]

@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

[v-deddai1-msft · Accepted solution]

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 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. 

[DataInsights · Accepted solution]

@Dhuey,

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:

[AllisonKennedy]

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

Count Result = IF(HASONEVALUE('Yr7 data'[Student Name]), COUNTROWS(FILTER('Yr7 data copy', 'Yr7 data copy'[Student Code] = SELECTEDVALUE('Yr7 data'[Student Code]))), 0)

Then put conditional formatting on the top chart using that measure.

Hope it helps, let me know if you have any questions on what I did. Just @ me as notifications aren't working great in this forum at the moment. 😄