Forum Discussion

kapil512's avatar
kapil512
Helper II
8 years ago
Solved

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

 

Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    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.

     

     

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

     

     

    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]

     

    3. Sort New_Answer_Choice column by New Order column.

     

     

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

     

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    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.

     

     

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

     

     

    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]

     

    3. Sort New_Answer_Choice column by New Order column.

     

     

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

     

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

    • kapil512's avatar
      kapil512
      Helper II
      8 years ago

      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..!

       

       

      please help me out in this case.

       

      Thanks,

      Kapil