Forum Discussion
Merge Cells
- 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
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.