Stacked Records as a table

[Magrfa]

Hello All, 

I am trying to work out how I would do this. I have two tables which are linked, one is a list of users and the other is the user profiles. I want to display a table visual with the profiles across the top and a list of users on that profile below them as in the image below

I also have a second use case which is very similar except they are linked by a unique identifier and the name is in a seperate column  so it would be in this form

[v-venuppu]

Hi @Magrfa ,

I wanted to check if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu · Accepted solution]

Hi @Magrfa ,

You can replicate your Excel layout using a Matrix visual in Power BI:

Create a relationship between Users[ProfileID] --> Profiles[ProfileID].

In the Matrix:

Columns - Profiles[ProfileName]

Rows - Users[UserID] (or Name)

Values - Users[Status] (set to Don’t summarize).

Apply conditional formatting on Status to color the cells based on values 1–7.

This way, profiles appear across the top, users are listed underneath, and their status controls the color - just like your Excel view, but dynamic in Power BI.

Thank you.

[v-venuppu]

Hi @Magrfa ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[Magrfa]

Thanks all for the ideas, No I have not yet got quite what I am after.

In my original the tables on the Left are the source the table on the right is how I am after to display it.

I originally had this in an Excel spredsheet managed manually, i was hoping to automate it with PowerBI, Here is a screenshot of the Excel layout, I have had to blurr it to protect the people's Names

In PowerBI I have:
A table with the User Profiles and a unique identifier (16 hexadecimal characters)
A Table of Users and against each user the unique identifier of the User Profile, Also a numeric value in another field 1-7

What I am after is to have the User Profiles across the top and then list the users underneath against the relevent User Profile, as per the Screenshot, then the numeric value of 1-7 indicated the status of the user and that controls the colour

[v-venuppu]

Hi @Magrfa ,

Thank you @sivarajan21 for the prompt response.

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[sivarajan21]

Hi @Magrfa 

Is this your expected output?

 I have created a dynamic dax measure to achieve this.

Dynamic Profile Value = 
VAR SelectedProfile = SELECTEDVALUE(Users[ProfileID])
RETURN
CALCULATE(
    FIRSTNONBLANK(Users[UserID], 1),
    Users[ProfileID] = SelectedProfile
)

Please let me know if it doesn't work

Best Regards,

[v-venuppu]

Hi @Magrfa ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Royel for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[Royel]

Hi @Magrfa  lets solve it by using power query (Create blank query)

Create Dataset1: 

let
    Source = Table.FromRows({
        {"A", "Name 1"},
        {"B", "Name 2"},
        {"C", "Name 3"}
    }, {"Label", "Class"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Label", type text}, 
        {"Class", type text}
    })
in
    #"Changed Type"

Dataset2: 

let
    Source = Table.FromRows({
        {1, "A"},
        {2, "C"},
        {3, "B"},
        {4, "B"},
        {5, "B"},
        {6, "B"},
        {7, "A"},
        {8, "C"},
        {9, "C"},
        {0, "C"}
    }, {"Value", "Label"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Value", Int64.Type}, 
        {"Label", type text}
    })
in
    #"Changed Type"

Data: 

let
    Dataset1 = #"Dataset1",
    Dataset2 = #"Dataset2",
    
    // Merge to get class names
    #"Merged Queries" = Table.NestedJoin(Dataset2, {"Label"}, Dataset1, {"Label"}, "Lookup", JoinKind.LeftOuter),
    #"Expanded Lookup" = Table.ExpandTableColumn(#"Merged Queries", "Lookup", {"Class"}),
    
    // Group by class and add sequential numbering within each group
    #"Grouped by Class" = Table.Group(#"Expanded Lookup", {"Class"}, {
        {"Data", each Table.AddIndexColumn(_, "GroupIndex", 1, 1, Int64.Type), type table}
    }),
    
    // Expand the grouped data
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped by Class", "Data", 
        {"Value", "GroupIndex"}),
    
    // Pivot by Class using GroupIndex as the row identifier
    #"Pivoted Column" = Table.Pivot(#"Expanded Data", 
        List.Distinct(#"Expanded Data"[Class]), 
        "Class", "Value"),
    
    // Remove the GroupIndex column
    #"Final Result" = Table.RemoveColumns(#"Pivoted Column", {"GroupIndex"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Final Result",{{"Name 1", Int64.Type}, {"Name 3", Int64.Type}, {"Name 2", Int64.Type}})
in
    #"Changed Type"

Output: 

https://drive.google.com/file/d/1pE9ivMLpXs-_1bzRcpuEpcFDw5gQdrTE/view?usp=drive_link 

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!