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
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
- sivasrao3 years agoHelper III
Great.
Thank you for the solution.😄👌
- sivasrao3 years agoHelper III
Hi PaulDBrown
Is any chance to insert some emojis based on dmood column data?
Ex: Very Happy: 😀
Not Happy: 😔
Neutral: 🙂
Thank you.