topic Re: Help with Concatenatex in DAX Commands and Tips

Help with Concatenatex

slice_dice — Wed, 28 Aug 2024 18:41:58 GMT

There is a video that shows how to use Concatenatex to join multiple items that are being aggregated in a pivot table. (https://www.youtube.com/watch?v=J6yxJ2uELxQ&t=470s).

However, it seems to be specific to the OneBigTable approach to modelling.

Wondering if there is a way to do with a star schema. Here is a graphic of what I mean:

Re: Help with Concatenatex

Dangar332 — Wed, 28 Aug 2024 18:53:04 GMT

Hi, slice_dice

Try below measure

measure = Var a = Values('Table'[Person]) return Concatenatex(a,'Table'[Person]," ,")

Re: Help with Concatenatex

slice_dice — Wed, 28 Aug 2024 20:18:55 GMT

Thanks Dangar332

I tried that and the Concat is combining all values ignoring row context. It should show "Tom,Mark" for South and "Jerry" for West in instead it shows all. Here are some screen shots

Re: Help with Concatenatex

gmsamborn — Wed, 28 Aug 2024 23:53:27 GMT

Hi slice_dice

Would a measure like this help?

Concatenatex = CONCATENATEX( 'Sales', RELATED( 'Person'[Person] ), ", " )

Let me know if you have any questions.

Re: Help with Concatenatex

Dangar332 — Thu, 29 Aug 2024 06:24:25 GMT

Hi, slice_dice

I have created measure using data you provided

The above measure is work perfect

You can download .PBIX file

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Help with Concatenatex

slice_dice — Thu, 29 Aug 2024 20:09:55 GMT

Dangar332,

Many thanks for your help on this.
I noticed the difference between the two models is your PBIX file has a 1 to 1 relationship between Dim_Person and Facts while my has a 1 to many.

See

Here is the data model from the PBIX file

Here is the data mode from my power pivot model

I see that your got the desired result but wondering if that result is possible modelled the way I have done with a 1 to many relationship from dim_person to facts?

Re: Help with Concatenatex

Dangar332 — Fri, 30 Aug 2024 15:07:51 GMT

Hi, slice_dice

Measure = var a = CALCULATETABLE(VALUES(Dim_person[Person]),CROSSFILTER(Facts[FK_person],Dim_person[Key],Both)) RETURN CONCATENATEX(a,Dim_person[Person],",")

Download below file

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.