Forum Discussion

sivasrao's avatar
sivasrao
Icon for Helper III rankHelper 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.

  • 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

4 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion

    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

    • sivasrao's avatar
      sivasrao
      Icon for Helper III rankHelper III

      Great.

      Thank you for the solution.😄👌

       

    • sivasrao's avatar
      sivasrao
      Icon for Helper III rankHelper III

      Hi PaulDBrown 

      Is any chance to insert some emojis based on dmood column data?

      Ex: Very Happy: ğŸ˜€

           Not Happy: ğŸ˜”

           Neutral: ğŸ™‚

       

      Thank you.

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion

    just use a matrix visual and stack your columns

    very happy will be your first column

    agree disagree etc will be your second column

     

    stack them on top of each other in the rows section

     

    its helpful if you show a sample of your data.