cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kapil512 Regular Visitor
Regular Visitor

Sorting the data on Duplicate data column using the order column

HI,

 

Could not able to sort the data based on two columns.

 

here is the my senario,i have  text column and sorting order columns like below(Which is coming from Database), that text colum data i am using bar chart on X axis, that should in sorting order which is in Database.

 

 when i click the sort the data based in ANSWER_CHOICE_ORDER, dupliate error is coming.

 

i have so many Questions and every question has different answer, some time answer will be the same for different question that order will chnage.

 

I have followed the below solution,but its not worked as expected.

 

i took all the distinct answer into one table and added the index column, but that time few of the questions that order will chnage.

 

if i select one question it will show in the report Answer choice and ordering the Answer_Choice_order.

 

Thanks for help..!

 

please let me know,if this question is not clear.

 

Thanks,

Kapil

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Sorting the data on Duplicate data column using the order column

Hi @kapil512,

i have so many Questions and every question has different answer, some time answer will be the same for different question that order will chnage.

In this scenario, you should be able to follow steps below to sort the answers properly for a specific question.

 

I assume you have a table called "Table1" like below.

 

t1.PNG

 

1. Put all Questions into one table and add an index column, and create a relationship between QUESTIONS table and "Table1".

 

t2.PNGre1.PNG

 

2. Use the formulas below to add two new columns, one for ANSWER_CHOICE_ORDER, another for ANSWER_CHOICE.

New_Answer_Choice = RELATED(QUESTIONS[INDEX]) & " - " & Table1[ANSWER_CHOICE]
New Order = RELATED(QUESTIONS[INDEX])*100+Table1[ANSWER_CHOICE_ORDER]

c1.PNG

 

3. Sort New_Answer_Choice column by New Order column.

 

s1.PNG

 

4. Then show the New_Answer_Choice column column as X axis on your bar chart instead.

 

r1.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

3 REPLIES 3
kapil512 Regular Visitor
Regular Visitor

Re: Sorting the data on Duplicate data column using the order column

survey.PNG

 

I miss the screenshot.

v-ljerr-msft Super Contributor
Super Contributor

Re: Sorting the data on Duplicate data column using the order column

Hi @kapil512,

i have so many Questions and every question has different answer, some time answer will be the same for different question that order will chnage.

In this scenario, you should be able to follow steps below to sort the answers properly for a specific question.

 

I assume you have a table called "Table1" like below.

 

t1.PNG

 

1. Put all Questions into one table and add an index column, and create a relationship between QUESTIONS table and "Table1".

 

t2.PNGre1.PNG

 

2. Use the formulas below to add two new columns, one for ANSWER_CHOICE_ORDER, another for ANSWER_CHOICE.

New_Answer_Choice = RELATED(QUESTIONS[INDEX]) & " - " & Table1[ANSWER_CHOICE]
New Order = RELATED(QUESTIONS[INDEX])*100+Table1[ANSWER_CHOICE_ORDER]

c1.PNG

 

3. Sort New_Answer_Choice column by New Order column.

 

s1.PNG

 

4. Then show the New_Answer_Choice column column as X axis on your bar chart instead.

 

r1.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

kapil512 Regular Visitor
Regular Visitor

Re: Sorting the data on Duplicate data column using the order column

Thank you so much your reposne..!

 

i changes the code above, in bar chart i want to display ANSWER_CHOICE,if i try to display i am getting the below error.

i am sorry for late reponse..!

 

Capture.PNG

 

please help me out in this case.

 

Thanks,

Kapil