Forum Discussion

ek2112's avatar
ek2112
Icon for Advocate II rankAdvocate II
8 years ago
Solved

GROUP BY calculated column

Hello,   I have the data in the following format - is there a way to extract all salesperson names and write them into a new calculated column for each invoice number? Thank you.  
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    ek2112

     

    May be

     

    Calculated_Column =
VAR InvoiceNo = [Invoice No]
RETURN
    CONCATENATEX (
        FILTER ( Sales, Sales[Invoice No] = InvoiceNo ),
        Sales[Salesperson],
        ", "
    )
  • ek2112's avatar
    ek2112
    8 years ago

    Zubair_Muhammad thanks for looking into this, this works, but coming up with duplicates. Is there a way to suppress duplicates?

  • Vvelarde's avatar
    Vvelarde
    8 years ago

    ek2112

     

    Hi, try with this:

     

    Column =
VAR Invoice = Table1[Invoice N]
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            DISTINCT ( Table1[Sales Person] ),
            FILTER ( Table1, Table1[Invoice N] = Invoice )
        ),
        Table1[Sales Person],
        ","
    )

    Regards

     

    Victor

ek2112's avatar
ek2112
Icon for Advocate II rankAdvocate II
8 years ago

Anonymous Thanks, I tried with the measure, but I get an error "table of multiple values was supplied where a single value was expected". Here is the sample pbix.

 

 

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

ek2112

 

May be

 

Calculated_Column =
VAR InvoiceNo = [Invoice No]
RETURN
    CONCATENATEX (
        FILTER ( Sales, Sales[Invoice No] = InvoiceNo ),
        Sales[Salesperson],
        ", "
    )
  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    8 years ago

    ek2112

     

    Hi, try with this:

     

    Column =
VAR Invoice = Table1[Invoice N]
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            DISTINCT ( Table1[Sales Person] ),
            FILTER ( Table1, Table1[Invoice N] = Invoice )
        ),
        Table1[Sales Person],
        ","
    )

    Regards

     

    Victor

  • ek2112's avatar
    ek2112
    Icon for Advocate II rankAdvocate II
    8 years ago

    Zubair_Muhammad thanks for looking into this, this works, but coming up with duplicates. Is there a way to suppress duplicates?