Forum Discussion

sivasrao's avatar
sivasrao
Helper III
3 years ago
Solved

Merge Cells

Hi Everyone, How can I get the same as the below image by using Power BI? How can merge the cells like in as above image? Thank you.
  • PaulDBrown's avatar
    3 years ago

    Assuming you have a fact table like the following:

    Create dimension tables for mood and state including a sorting order column using:

    Mood table = 
    SELECTCOLUMNS(
        DISTINCT('Table'[Mood]), "dMood", 'Table'[Mood],
        "Order", 
        SWITCH(TRUE(),
        CONTAINSSTRING('Table'[Mood], "Very"), 1,
        CONTAINSSTRING('Table'[Mood], "Neut"), 2,
        3))

    State table = 
    SELECTCOLUMNS(
        DISTINCT(
            'Table'[State]),
            "dState", 'Table'[State],
            "Order",
            SWITCH(TRUE(),
            CONTAINSSTRING('Table'[State], "I ag"), 1,
            CONTAINSSTRING('Table'[State], "Strongly ag"), 2,
            CONTAINSSTRINGEXACT('Table'[State], "Satisfied"), 3,
            CONTAINSSTRINGEXACT('Table'[State], "Strongly satisfied"), 4,
            CONTAINSSTRING('Table'[State], "More"), 5,
            CONTAINSSTRING('Table'[State], "I dis"), 6,
            CONTAINSSTRING('Table'[State], "Strongly dis"), 7,
            CONTAINSSTRING('Table'[State], "Strongly uns"), 8,
            10))

    Sort the mood and state fields by the orders columns using this method:

    Create one-to-many relationships between the dimension tables and the corresponding fields in the fact table. The model looks like this:

    Create a matrix using the fields from the dimension table and the item field in the rows bucket. Expand all levels and under Row headers in the formatting pane turn off the +/- icons and turn off stepped layout under options

     Sample PBIX file attached