Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

Use distinct measure values as columns in matrix

Hi,

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:

NEW_CLUB

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!

3 REPLIES 3
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).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

New Member

here is some sample data:

 Person ID Main Site Month Year Total_Visits_to_Clubs Club_1_Visits Club_2_Visits Club_3_Visits 1 Club_3 1 2023 27 0 12 15 1 Club_3 2 2023 22 0 13 9 1 Club_3 3 2023 27 0 14 13 2 Club_2 1 2023 11 11 0 0 2 Club_2 2 2023 13 13 0 0 2 Club_2 3 2023 16 16 0 0 3 Club_1 1 2023 8 7 1 0 3 Club_1 2 2023 7 1 7 0 3 Club_1 3 2023 7 7 0 0 4 Club_2 1 2023 29 0 0 29 4 Club_2 2 2023 25 0 0 25 4 Club_2 3 2023 28 0 0 28 5 Club_3 1 2023 15 0 10 5 5 Club_3 2 2023 11 1 8 2 5 Club_3 3 2023 9 0 5 4

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:

 Club_1 Club_2 Club_3 Club_1 1 0 0 Club_2 1 0 1 Club_3 0 0 2

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

 Club_1 Club_2 Club_3 Club_1 1 0 0 Club_2 1 0 1 Club_3 0 1 1

Hope that makes sense.

Super User

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

Helpful resources

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors