Create a new column from other column

[jlarques]

Dear all,

I have a table with many millions of rows and I want to extract a data master to connect it with the budget table. 

The DAX formula is:

KIT CATEGORY=
      DISTINCT(

           SELECTEDCOLUMNS(

                KIT;

                "COUNTRY"; KIT[COUNTRY];

                "PRODUCT";KIT[PRODUCT];

                "TYPE"; KIT[TYPE]

            )

       )

But an error message appears related to the relationship:

Apologies because it's in Spanish.

Thank you to everybody!! 

[Icey]

Hi @jlarques ,

Glad to hear that. Please accept your solution above so that people who may have the same question can get the solution directly.

Best Regards,

Icey

[jlarques]

Hi @az38 

yes, I'm sure, but let me check with time and I'll tell you something.

Thanks for all your help and time! You gave me an answer very fast! I'll mark your solution as a solution if it's working well.

Thanks again!

[jlarques]

@az38 

same error, but don't worry. I'll try to put it in another way. I thought was easier to get the data from another table to have a master.

Thanks again for all your help! I'm going to fight with this issue and I'll publish the solution if I find it!

Regards,

José Luis

[jlarques]

@az38 

thanks again for your quick answer last week.

Finally, I spoke with the IT department to modify the data source and I got the data exactly as I wanted it.

Again, very grateful for all your help.

Regards,

[az38 · Accepted solution]

@jlarques 

modifying data source is the best solution ever 🙂

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38]

Hi @jlarques 

show us relationship between tables please. the issue is somewhere around it

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[jlarques]

Hi @az38

it was no relationship but I created:

- From Kit[Product] to Kit Category[Product] many to one.

The error only appears when I want to add the Type column. If I deleted it, I have a table named Kit Category with two columns; Country and Product.

Thanks for your help.

[az38]

so, @jlarques 

as i understand it works without type column because each product in your data source has the only category.

but when you add "type" column, you get a few rows with the same product and category but with different types

as you have a many-to-one relations it became impossible, because many product from Kit table have relations with many products from Kit Category

for my point, the best solution is to create in Kit table calculated column 

SurrogateKey = concatenate('Table'[Product];concatenate('Table'[Category];'Table'[Type]))

then try to build a master table:

KIT CATEGORY=
      DISTINCT(
                KIT[SurrogateKey]
       )

and create a relationship many-to-one by this SurrogateKey field

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[jlarques]

Hi @az38 

thanks for your quick answer.

When I try to create a calculated column on the KIT CATEGORY table, the Concatenated formula cannot show me any table, only the KIT CATEGORY fields, so, I cannot add other external columns from the KIT table.

Any ideas?

Thanks again.

[az38]

@jlarques 

no, you should create calculated column in Kit table, in your origin. then create distinct by this new column

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[jlarques]

@az38 

When I create a new column with the DISTINCT formula, an error appears with this text:
"A table of several values ​​was provided, where a single value was expected".

Thanks again!

[az38]

@jlarques are you sure you do everything correct? maybe you have an empty cells in one of columns?

and be sure, your new table has is defined as

KIT CATEGORY = 
      DISTINCT(
                KIT[SurrogateKey]
       )

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin