Help needed with: Concatenatex, distinct and ralatedtable

[Mueez]

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."

[AlB · Accepted solution]

@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.

[Mueez]

Awesome! It works! It works!!!

Thanks a million!

[AlB]

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.

[Mueez]

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.

[AlB]

@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.

[Mueez]

The aim is to eliminate yellow highlighted duplicates.

[Mueez]

@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