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
ccolletti's avatar
ccolletti
Helper I
3 years ago

sevenhills Thank you for your help, this halfway helped me out.

 

Using the new measure within the table for the State worked perfectly, however the store is still not working correctly. Not sure what I'm missing as I did the fromula exactly how you wrote it out for both and the States came back fine, but the store still just pulled in every single store number.

 

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!