Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kapil512
Helper II
Helper II

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
v-ljerr-msft
Employee
Employee

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

View solution in original post

3 REPLIES 3
v-ljerr-msft
Employee
Employee

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

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

kapil512
Helper II
Helper II

survey.PNG

 

I miss the screenshot.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.