Calculated Column needs to be created based on two existing Column Dimensions

[Anonymous]

I have 2 columns in my data set Column A and Column B. I need to create a calculated column based on the combination of Column A and Column B. In a normal scenario a nested IF statement will be enough.

But in real time i have 5 distinct values in Column A and around 70 distinct values in Column B and there are around 140+ combinations of both the column data in the data set. The users have provided the mapping for these 140+ combinations.

I have somehow managed to manyally input all the combinations in a nested IF statement but that is not frasible and may have lot fo errors and hard to maintain. 

Need help to simplify this and assign to the calculated column output. Kindly suggest best ways to do it.

Sample DataSet for reference:

[v-eqin-msft]

Hi @Anonymous ,

Actually we need to know the logic of the new calculated column so that we could improve the performance effectively, please provide more details.

Best Regards,
Eyelyn Qin

[Anonymous]

How about using the excel sheet itself as a data source?

[Anonymous]

Data source is SQL Database and there are thouands of records. 

[Anonymous]

Got it, if those mappings are final then we can import it into power bi and may be create a relationship with other tables that might need it. 

If the categories are less in numbers then we can go for switch statement but seems like there are many categories, therefore the best thing would be to load it and connect with other tables.

[selimovd]

Hey @Anonymous ,

so what exactly do you want to check?

Can you give an example?

Best regards

Denis

[Anonymous]

Hi Denis,

               I just gave a sample example for my requirement. Currntly i have writted n Nested IF iteration for the 140 actual Coulmn A & Column B combinations for the necessary output. That is not feasible and practical. I just want to identify a easier approach to get the values assigned to the calcuated column.