Slicer to control what columns a measure calculated against

[ArzanP]

I have an interesting problem statement.

I'm making somethig as a project that relates to NBA stats. Basically I would like to compare TWO team playing against each other against a few key measures. However, those measures relate to periods of each game (where i have values stored in columns) . For simplicity.

Columns look like

Team - Q1 Score - Q2 Score - Q3 Score - 1H Score - 2H Score - FullGame Score

I want to use two slicers, one to select the home and away team and then one to select the period (1Q-4Q, 1st Half or 2Nd Half or Full Game). Based on user selection in the SLICER, i want the measures to change which column they are referncing. For exampe

Score For: =average(Q1ScoreFor)

Score Against: = average(Q1ScoreAgainst)

If the user selects 2nd quarter, I want that (Q1ScoreFor and Q1ScoreAgainst) to change to  (Q2ScoreFor and Q2ScoreAgainst).

Any thoughts on whether this is possible?

[v-ljerr-msft]

Hi @ArzanP,

In this scenario, I would suggest unpivot the Q1 Score - Q2 Score - Q3 Score - 1H Score - 2H Score - FullGame Score columns first. After unpivoting, the new table should be like below.

Team	Attribute	Value
	Q1 Score
	Q2 Score
	Q3 Score
	1H Score
	2H Score
	FullGame Score

Then should be able to simply use the Attribute column as Slicer, and your measure will be filtered without any change. 

In addition, for more details about how to use Unpivot Columns option in Power BI, you can refer to this article. 

Regards