Create Unique Index Of Two Fields Yields Duplicates

[UncleLewis]

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

[TomMartens]

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 · Accepted solution]

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])