Help with Concatenatex

[slice_dice]

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:

[gmsamborn]

Hi @slice_dice 

Would a measure like this help?

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

Let me know if you have any questions.

[Dangar332]

Hi, @slice_dice 

Try below measure

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

[slice_dice]

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

[Dangar332]

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.

[slice_dice]

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?

[Dangar332]

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.