Forum Discussion

UncleLewis's avatar
UncleLewis
Icon for Responsive Resident rankResponsive Resident
3 years ago
Solved

Create Unique Index Of Two Fields Yields Duplicates

Hi,

I have data from a survey.
The survey has ~350 questions..

There are variable number of responses to choose from based on the question.

One question is which state do you work so there are 52 possible responses (Territories)

I created an index for the questions and a separate index for the responses.

The responses re-seed at 1 for every new question.

 

I tried to concatenate the question index with the response index so for the state question, this gave me complete Index values

2.1, 2.2, 2.3, 2.4, 2.5, 2.6, 2.7, 2.8. 2.9 2.10, ...

I then converted the data type to decimal and now I have duplicates at 2.1, 2.2, 2.3, 2.4, and 2.5

 

How can I create a unique non-repeating index in this case?

 

Thanks,

-w

Need Help
  • UncleLewis's avatar
    UncleLewis
    3 years ago

    Thanks Tom,

    I ended up mutiplying by 0.01 for all values less than 10.
    I think this pattern will work for all combinations of Questions/Answers, not just this case where there are 52 possible responses.

    Thanks,
    -w

    if
    [PK] < 10
    then
    Number.ToText( [Group] + ( 0.01 * [PK]))
    else
    Number.ToText([Group]) & "." & Number.ToText([PK])

     

2 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    3 years ago

    Hey UncleLewis ,

    I recommend this article: https://www.daxpatterns.com/survey/

    Maybe you have to check your ToDecimal conversion, as "2.4" and "2.40" will be converted to the same decimal number:2.4. I would suggest that you convert the answers to a fraction, meaning "2.4" will become "2.04" and "2.40" will become 2.4.

    Hopefully, this provides you with an ideas of how to tackle your challenge.

    Regards,
    Tom




    • UncleLewis's avatar
      UncleLewis
      Icon for Responsive Resident rankResponsive Resident
      3 years ago

      Thanks Tom,

      I ended up mutiplying by 0.01 for all values less than 10.
      I think this pattern will work for all combinations of Questions/Answers, not just this case where there are 52 possible responses.

      Thanks,
      -w

      if
      [PK] < 10
      then
      Number.ToText( [Group] + ( 0.01 * [PK]))
      else
      Number.ToText([Group]) & "." & Number.ToText([PK])