Change column colour on histogram bar chart based on a filter

[dphillips]

I have a list of marks for students. I have created a table which puts those marks into a number of bins. See bucket table below.

I then use a calculated column to put the marks into one of the columns.

Rep Mark Range = CALCULATE(VALUES(MarkBucketTable[MarkBucketName]),filter(MarkBucketTable, MarkBucketTable[Start]<=uncRedshift_Studentresults[mark] && MarkBucketTable[End]>=uncRedshift_Studentresults[mark]))

 I then use a bar graph to get a histogram of marks. I want to be able to click on a student in a slicer and for the relevant column to change colour so that it is clear to see what range of marks the student is sitting in. For example, in the image below, if student 56094 had a mark of 86, I would like the column indicated to be a different colour to highlight where he sits in the group. Note: The slicer is set up so that it does not filter the bar chart selection so that I can see the results of all students, not just the one selected.

If anyone has an idea how to do this, I would be most grateful.  Data files included.

Marks Bucket Data 

Report Mark Data 

Thanks.

[Greg_Deckler]

I do not know of a method by which you can accomplish that off hand. Maybe if you use the Disconnected Table Trick and use that for you Legend and create some kind of measure to have the desired value be in one category or other of the legend? 

Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[dphillips]

Thanks @Greg_Deckler for the suggestion. Created a disconnected table which looks like below. 

Then created measures for each of the mark ranges eg 

80to84 = IF(MAX(uncRedshift_Studentresults[mark])>=80 && MAX(uncRedshift_Studentresults[mark])<=84,"80 to 84",BLANK())

Finally here is the measure which brings it all together.

Measures to Show = 
IF(
	HASONEVALUE(uncRedshift_Studentresults[mark]),  
		IF(HASONEVALUE(MeasureTable[MeasureBucket]),
			SWITCH(
          			VALUES(MeasureTable[MeasureBucket]),
                    "95 to 100", MeasureTable[95to100],
                    "90 to 94",MeasureTable[90to94],
                    "85 to 89", MeasureTable[85to89],
                    "80 to 84",MeasureTable[80to84],
                    "75 to 79",MeasureTable[75to79],
                    "70 to 74",MeasureTable[70to74],
                    "65 to 69",MeasureTable[65to69],
          			"60 to 64",MeasureTable[60to64],
                    "55 to 59",MeasureTable[55to59],
          			"50 to 54",MeasureTable[50to54],
                    "Below 50",MeasureTable[Below50]
	 			),
                        // If the Student ID slicer has not been selected, just display an empty string
			""
		)
)

Unfortunately, I can't use this measure in a legend in a bar chart at all - PowerBI does not let me drag this measure into the legend area at all. (Can you use a measure as a legend?)

Any thoughts on how I can get this to work? Thanks again for your help.

[Greg_Deckler]

Use your disconnected table as the legend. The measure is there to serve as the bridge between the disconnected table and the rest of your data model.