topic Repeating "Key" column value in Calculated Table when merging two columns from Source Table into one in DAX Commands and Tips

Repeating "Key" column value in Calculated Table when merging two columns from Source Table into one

devitus — Wed, 01 Dec 2021 10:11:40 GMT

Hi,

I am trying to create a new calculated table based on another table:

"ASINs" and "SKUs" columns from source table would be combined into a single "ASINs & SKUs" column of the calculated table (distinct values and excluding blanks from both "ASINs" and "SKUs" source columns), while repeating the respective "Key" column. Other columns (Data 1, 2, 3..) would be ignored.

I have tried the following, but the expression didn't work claiming "A table of multiple values was supplied where a single value was expected."

Calculated Table = SELECTCOLUMNS(Source Table, "Key", Source Table[Key], "ASINs & SKUs", UNION(VALUES(Source Table[ASIN]), VALUES(Source Table[SKU])))

Thanks in advance for your help!

Re: Repeating "Key" column value in Calculated Table when merging two columns from Source Table into one

PaulOlding — Wed, 01 Dec 2021 12:51:26 GMT

Hi devitus

The UNION function needs you to supply the whole table, not just a particular column

Try this:

VAR _Unioned = UNION( SELECTCOLUMNS( FILTER('Table', NOT ISBLANK('Table'[ASINs])), "Key", 'Table'[Key], "ASINS & SKUs", 'Table'[ASINs] ), SELECTCOLUMNS( FILTER('Table', NOT ISBLANK('Table'[SKUs])), "Key", 'Table'[Key], "ASINS & SKUs", 'Table'[SKUs]) ) VAR _Distinct = SUMMARIZE( _Unioned, [Key], [ASINS & SKUs] )

Re: Repeating "Key" column value in Calculated Table when merging two columns from Source Table into one

devitus — Wed, 01 Dec 2021 13:11:49 GMT

Absolutely amazing, thanks so much PaulOlding! Worked perfectly 🙂