Forum Discussion
Power BI Static rows in Matrix Visual
I have created below matrix using a table having tickets data with fields as Resolution Breached Final and Priority . Resolution Breached Final contains two values as "SLA Met" and " Breached" and Priority has"P1","P2","P3" AND "P4". Percentage below the priorities is a matrix which calculates % of total tickets meeting SLA/ Total number of tickets
I Need to have static rows above Breached row in the above matrix ,which contains below data. ESL and MSl data will not change.
How do i achieve this ?
Hi mzutshi ,
We can create a new table for the rows of the matrix like this.You can refer to M-code like this:
let Source = Table.FromList(List.Distinct(Table[Resolution Breached Final]) & {"ESL","MSL"}, null, {"Resolution Breached Final"}), #"Added Conditional Column" = Table.AddColumn(Source, "Sort", each if [Resolution Breached Final] = "ESL" then 0 else if [Resolution Breached Final] = "MSL" then 1 else if [Resolution Breached Final] = "Breached" then 2 else if [Resolution Breached Final] = "SLA Met" then 3 else null) in #"Added Conditional Column"And the relationships:
Then please create a new measure.
NewMeasure = VAR __cur_row = SELECTEDVALUE('Table2'[Resolution Breached Final]) VAR __cur_col = SELECTEDVALUE('Table'[Priority]) VAR __result = SWITCH( TRUE(), // [Measure] is the value field of the original matrix __cur_row IN {"Breached","SLA Met"},CALCULATE( [Measure], 'Table'[Resolution Breached Final]=__cur_row ), __cur_row = "ESL", SWITCH( __cur_col, "P1", 1, "P2", 0.92, "P3", 0.96, "P4", 0.99), __cur_row = "MSL", SWITCH( __cur_col, "P1", 0.9, "P2", 0.9, "P3", 0.92, "P4", 0.95) ) RETURN __resultOutput:
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
2 Replies
Hi mzutshi ,
We can create a new table for the rows of the matrix like this.You can refer to M-code like this:
let Source = Table.FromList(List.Distinct(Table[Resolution Breached Final]) & {"ESL","MSL"}, null, {"Resolution Breached Final"}), #"Added Conditional Column" = Table.AddColumn(Source, "Sort", each if [Resolution Breached Final] = "ESL" then 0 else if [Resolution Breached Final] = "MSL" then 1 else if [Resolution Breached Final] = "Breached" then 2 else if [Resolution Breached Final] = "SLA Met" then 3 else null) in #"Added Conditional Column"And the relationships:
Then please create a new measure.
NewMeasure = VAR __cur_row = SELECTEDVALUE('Table2'[Resolution Breached Final]) VAR __cur_col = SELECTEDVALUE('Table'[Priority]) VAR __result = SWITCH( TRUE(), // [Measure] is the value field of the original matrix __cur_row IN {"Breached","SLA Met"},CALCULATE( [Measure], 'Table'[Resolution Breached Final]=__cur_row ), __cur_row = "ESL", SWITCH( __cur_col, "P1", 1, "P2", 0.92, "P3", 0.96, "P4", 0.99), __cur_row = "MSL", SWITCH( __cur_col, "P1", 0.9, "P2", 0.9, "P3", 0.92, "P4", 0.95) ) RETURN __resultOutput:
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
Thank you so much Anonymous
