How to transform the display table

[harryli]

As you may see from the above figures, the question is I would like to transform the table to be the following:

Total Bed 465

OC           89

FR            236

NOP        140

Thanks.

[johncolley · Accepted solution]

Hi @harryli ,

You can accomplish this with a disconnected (no relationships to other tables) table that holds the values of the measures you want to display. The order column allows you to set a custom order.

Then use a switch statement to display the correct value.

displaycolumn = 
var selection = SELECTEDVALUE('Measures (2)'[Measure])
RETURN
SWITCH(
TRUE(),
Selection = "Total Bed", [Total Bed],
Selection = "OC", [OC],
Selection = "FR", [FR],
Selection = "NOP", [NOP])

Then create a table with the Measure column from the disconnected table and the displaycolumn measure.

I hope this solves your question - if it does please mark it as a solution!

[v-jingzhang]

Hi @harryli 

Have you tried the solutions provided by other two users? They should work well. For the second solution with Switch values to rows option, it is available in Matrix visual rather than Table visual.

Best Regards,
Community Support Team _ Jing

[Signore_Ands]

Hi @harryli - is it not a case of changing the setting "switch values to rows"?

That may do the trick.

[harryli]

Thanks for your message, but I cannot find the "Options" in the dashboard.

[johncolley]

Hi @harryli ,

That option is only available when using the matrix visual, not the table visual as you currently are using.

[harryli]

Hi @johncolley ,

That means I need to build the matrix table* to store each of the values I want to display. Or are there any methods to transform the table visual?

*matrix table like the following figure

Thanks a lot.

[johncolley · Accepted solution]

Hi @harryli ,

No, to follow the solution that Signore_Ands provided you just need to change your table to a matrix in the visualisation pane. Then the "Options" will be available to you.

[harryli]

Thanks, and it works.

[johncolley · Accepted solution]

Hi @harryli ,

You can accomplish this with a disconnected (no relationships to other tables) table that holds the values of the measures you want to display. The order column allows you to set a custom order.

Then use a switch statement to display the correct value.

displaycolumn = 
var selection = SELECTEDVALUE('Measures (2)'[Measure])
RETURN
SWITCH(
TRUE(),
Selection = "Total Bed", [Total Bed],
Selection = "OC", [OC],
Selection = "FR", [FR],
Selection = "NOP", [NOP])

Then create a table with the Measure column from the disconnected table and the displaycolumn measure.

I hope this solves your question - if it does please mark it as a solution!