cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper V

## 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

1 ACCEPTED SOLUTION
Helper V

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 2
Super User

Hey @UncleLewis ,

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper V

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors