Forum Discussion

ccolletti's avatar
ccolletti
Helper I
3 years ago

Combining Data, Separated by a Comma when Matching value in One Column in Power BI Desktop

Hi All,   I'm working on building a rollup of data to match Vendors to the State and Store numbers they work in. I was able to pull this data in Excel using TextJoin, but I want to utilize Power BI...
Need Help
sevenhills's avatar
sevenhills
Super User
3 years ago

ccolletti , the logic is same and not sure where it went wrong. Can you post the DAX?

 

Simple thumb rule: it should work for both the same way. As you have seen, DAX is simple code. All we are doing is getting distinct and concatenex. 

ccolletti's avatar
ccolletti
Helper I
3 years ago

sevenhills, agreed I figured it would work with State working. And not able to post in DAX, table is locked down by IT. Only have the ability to access the data for design purposes.

  • sevenhills's avatar
    sevenhills
    Super User
    3 years ago

    It is not possible to help without knowing the table, dax and the issue.

     

    Let us do Power Query route:
    Ask them to try in Power Query and see if that works there?

     

    Say, you have table as below:

     

    Note: Store is marked as text.

     

    Add a group by step as below:

    = Table.Group(#"Changed Type", {"Vendor"}, 
        {
         {"States", each Text.Combine ( List.Distinct([State], Comparer.OrdinalIgnoreCase), ", "), type text}
         , {"Stores", each Text.Combine ( List.Distinct([Store], Comparer.OrdinalIgnoreCase), ", "), type text}
        })

     

    and you can see the output as

     

    Another advantage is Power Query stores, where as Measure calculates in run time. 

    Hope this helps!