Showing results for 
Search instead for 
Did you mean: 
New Member

Use distinct measure values as columns in matrix



There are a few steps to my question, so i will try to be as clear as possible.


I have a table that looks like this:

ID_Number      YEAR      MONTH       HOME_CLUB     CLUB_1_VISITS     CLUB_2_VISITS       CLUB_3_VISITS     

1                      2023       Jan                CLUB_1              1                          5                             0                         

1                      2023       Feb               CLUB_1              2                          7                             1                         

1                      2023       Mar               CLUB_1              0                          8                             2                         

2                      2023       Jan                CLUB_2              7                          5                             20                         

2                      2023       Feb               CLUB_2              6                           5                            20                         

2                      2023       Mar               CLUB_2              8                          2                             11                         


My goal is to reallocate each person to a different club if more than 60% of their visits are to that club, and I want this to ideally by calculated over a selected time period.


For example - if only March was selected:

- person with ID_Number 1 would be reallocated to CLUB_2 (10 visits in total, 8 to CLUB_2 - 80%)

- person with ID_Number 2 would remain at CLUB_3 (21 visits in total - didnt visit any club more than 60%)


If Jan, Feb and March were selected:

- person with ID_number 1 would be reallocated to CLUB_2 (26 visits in total, 20 to CLUB_2 - 76%)

- person with ID_number 2 would be reallocated to CLUB_3 (84 visits in total, 51 to CLUB_3 - 61%)


I then want to create a matrix where, rows are HOME_CLUB, columns are NEW_CLUB and the values are the number of people that fit that. Everyone should only appear once in the matrix. So for example in Jan, Feb and Mar we would have:



HOME_CLUB      CLUB_1     CLUB_2     CLUB_3

CLUB_1              0                1               0

CLUB_2              0                0               1

CLUB_3              0                0               0


I have created a measure which finds the new club and based on selected values for year and month which is working correctly, I can see this if i create a table/matrix where PERSON_ID is its own column.


I also attempted to use the SWITCH method to convert the measure into a column, but this means the club is reallocated for each individual month, rather than for the whole selected period. This means that when for example 3 months are selected, some people are reallocated to a different club in Jan to what they would be for Feb and March and therefore are counted more than once in the matrix. 


I am just having trouble with the final step of using the measure value as a column in the matrix, is this possible at all?


Any help is appreciated!

Super User
Super User

yes, all custom matrix visuals are pretty much based on that option  (omitting the column values and using individual meaures instead).  But depending on your scenario this might not be necessary.


Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Please show the expected outcome based on the sample data you provided.

here is some sample data:


Person IDMain SiteMonthYearTotal_Visits_to_ClubsClub_1_VisitsClub_2_VisitsClub_3_Visits


This is where i'm up to in terms of using measures: 

the measures are used to establish the 'new main club'. The current matrix shows where each person has been reallocated to. You'll notice that if different months are selected there will be some changes to some peoples 'new main club'. The idea is that any months can be selected and based on the selected months the reallocation occurs.


What i want to do now is create a matrix where the values in 'Main Club' are the rows and the values returned in 'New main club' are the columns. The values in the matrix will be the number of people in them. For example if only March is selected it will look like this:




But if Jan, Feb and March are selected it will be different:




Hope that makes sense.

Have a look at Page 2. This is done with some unpivoting and implicit measures. No DAX was harmed during the process.

Helpful resources

Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors