How do I set the column order and show columns even if there are zero responses

[anita_conlon]

Hello,

I am new to Power BI and business intelligence in general.

I am using survey data where the options are;

• Strongly agree
• Agree
• Neither agree or disagree
• Disagree
• Strongly disagree
• N/A

I want to set the order of these columns and I want them to show each column even if there are zero responses.

Any help is greatly appreciated!

[Icey · Accepted solution]

Hi @anita_conlon ,

I speculate that your data structure might look like this:

Sample ID	Gender	Age	Question 1	Question 2	Question 3
1	M	18-25	Strongly agree	N/A	N/A
2	F	18-25	Agree	Strongly agree	N/A
3	M	25-30	Neither agree or disagree	Agree	Strongly agree
4	F	25-30	N/A	Neither agree or disagree	Agree
5	F	30-35	N/A	Strongly disagree	Neither agree or disagree
6	M	30-35	N/A	Strongly disagree	Neither agree or disagree
7	M	35-40	Agree	Strongly disagree	Neither agree or disagree
8	M	35-40	Agree	Strongly disagree	Neither agree or disagree
9	M	40-45	Agree	Strongly disagree	Neither agree or disagree
10	F	40-45	Agree	Strongly disagree	Neither agree or disagree

If so, try tp UnPivot your columns first:

Then, close and apply.

In report view, create a measure and a Matrix visual like so:

Count of Sample ID = COUNT('Sample'[Sample ID])+0

To solve the above problems, we need to create a new table, like what @az38 created.

Enter data manually and sort [Attitude] column by [Order] column:

Then, create relationship between the two tables:

Replace 'Sample'[Attitude] with 'Attitude'[Attitude] in the matrix visual:

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @anita_conlon 

if you need to sort column in Matrix visualand also to provide a column visualization even there is zero value I would recommend you:

1. create a table (for example with Enter Data button) "Survey Options". There will be 2 columns and 6 rows:

Option	Order
Strongly agree	1
Agree	2
Neither agree or disagree	3
Disagree	4
Strongly disagree	5
N/A	6

2. Use the technique which @Anand24  mentioned - make Sort By "Order" column in column tools

3. Create relationships between this new table and your origin table by Option field

4. For matrix visual create use all rows and values from your origin table, but Columns from the new one

[Anand24]

Hi @anita_conlon ,

If you are taking Table visualization or Matrix visualization, you can simply sort by clicking on the column name like below:

Press Ctrl and then click for advanced/nested sorting.

For any visualization(including table and matrix) in general, you can sort using below:

By default, each column will show will show even if it has zero responses or blank/NULL responses.

However if you face any difficulty regarding showing null values, you can enable/check below option:

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Anonymous]

Hey @anita_conlon ,

Create a calculted column:

SWITCH(
          TRUE(),
          [Text_Result_Column] = 'Strongly agree' ,1,
          [Text_Result_Column] = 'Agree' ,2,

          [Text_Result_Column] = 'Neither agree or disagree' ,3,

          [Text_Result_Column] = 'Disagree' ,4,

          [Text_Result_Column] = 'Strongly Disagree' ,5,

          [Text_Result_Column] = 'N/A' ,6,
          Blank())

Then, in the power query mark your [Text_Result_Column] and press "sort by column" and choose the new calculated column.
You can follow the instructions for the sort here:
https://databear.com/power-bi-tips-sort-by-month-name/

Ariel

[amitchandak]

@anita_conlon , not very clear. You can create a sort and sort on that.

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column