topic Re: Help needed with: Concatenatex, distinct and ralatedtable in DAX Commands and Tips

Help needed with: Concatenatex, distinct and ralatedtable

Mueez — Sat, 20 Mar 2021 19:28:00 GMT

Hi,

I am struggling with a pretty basic formula. This is the code I'm testing on daxstudio:

ADDCOLUMNS (
    ExternalVendorDocNumber,
    "new",
        CONCATENATEX (
            RELATEDTABLE ( GLandPostedPurchaseInvoiceLines ),
            DISTINCT(GLandPostedPurchaseInvoiceLines[Document No.]),
            ", "
        )
)

Addcolumns is just to give a row context. What I don't get is why Distinct isn't working here? This works fine if I don't use Distinct but ofcourse I just need the distinct from the column.

I've also tried using all and values but ofcourse all these functions give the same error " A table of multiple values was supplied where a single value was expected."

Re: Help needed with: Concatenatex, distinct and ralatedtable

AlB — Sat, 20 Mar 2021 21:55:45 GMT

Hi Mueez

Why would you need the DISTINCT()? CONCATENATEX is an iterator and expects a scalar as second argument, so using DISTINCT() will not work.

https://dax.guide/concatenatex/

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Help needed with: Concatenatex, distinct and ralatedtable

Mueez — Sun, 21 Mar 2021 00:30:12 GMT

Thanks @AlB for replying.

I need CONCATENATEX to get me just unique values as an answer. Final answer is basically supposed to look like a unique concatenation of all values of a column of the RELATEDTABLE.

Re: Help needed with: Concatenatex, distinct and ralatedtable

AlB — Sun, 21 Mar 2021 11:34:19 GMT

Mueez

Sure, I didn't say you don't need CONCATENATEX, I said you don't need the DISTINCT

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Help needed with: Concatenatex, distinct and ralatedtable

Mueez — Sun, 21 Mar 2021 16:48:23 GMT

AlB
There are duplicates in the result that second part (expression) of CONCATENATEX is giving. I don't know how to remove those duplicate values

Re: Help needed with: Concatenatex, distinct and ralatedtable

AlB — Sun, 21 Mar 2021 16:55:52 GMT

Mueez

I haven't looked in detail but try these. If it doesn't work , share the pbix with the expected result

ADDCOLUMNS ( ExternalVendorDocNumber, "new", CONCATENATEX ( DISTINCT ( RELATEDTABLE ( GLandPostedPurchaseInvoiceLines ) ), GLandPostedPurchaseInvoiceLines[Document No.], ", " ) )

ADDCOLUMNS ( ExternalVendorDocNumber, "new", CONCATENATEX ( CALCULATETABLE ( DISTINCT ( GLandPostedPurchaseInvoiceLines[Document No.] ), RELATEDTABLE ( GLandPostedPurchaseInvoiceLines ) ), GLandPostedPurchaseInvoiceLines[Document No.], ", " ) )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Help needed with: Concatenatex, distinct and ralatedtable

Mueez — Sun, 21 Mar 2021 17:44:53 GMT

The aim is to eliminate yellow highlighted duplicates.

Re: Help needed with: Concatenatex, distinct and ralatedtable

Mueez — Sun, 21 Mar 2021 17:56:20 GMT

Awesome! It works! It works!!!

Thanks a million!