Need help: Concatenate Text grouped by site, selected by date

[SebastianAlmer]

Hi,

I want to concatenate some text according to a specific date and group by a specific project-

The tables:

Personnel

PNR	Firstname	Lastname	Shortname (calculated)
1	Hans	Meier	Meier, H
2	Peter	Huber	Huber, P
3	Frank	Schulz	Schulz, F
4	Martha	Doe	Doe, M

Shortname = Personnel[Lastname] & ", " & LEFT(Personnel[Firstname],1)

PNR_Projects

PNR	Date	Project
1	01.01.2021	4711
2	01.01.2021	4712
3	01.01.2021	4713
4	01.01.2021	4711
1	02.01.2021	4715
3	02.01.2021	4711
4	02.01.2021	4711
1	03.01.2021	4712
4	03.01.2021	4715
3	04.01.2021	4714

Projects:

Project_Number	Project_Name
4711	Construction Site A
4712	Highway XY
4713	Building AB
4714	Project Whatever
4715	Site Whereever

Of course I can create a matrix, where I filter all by a date slicer and group by project.

this would be something like:


I want a more condensed view: How can I concatenate all Shortnames in one line?
I would like to have something like:

Construction Site A:
Doe, M | Schulz, F | ....

Site Whatever:
Meier, H | ...

So all Names on a specific Date grouped by Proejct, concatenated with a separator like " | "

any ideas? The quick measure "concatenate" leads to sam weird result:

Thanks in advance for your help

[Payeras_BI · Accepted solution]

Hi @SebastianAlmer ,

Something like this:

CONCATENATED SHORT NAMES =
CALCULATE (
    CONCATENATEX (
        VALUES ( Personnel[Shortname (calculated)] );
        Personnel[Shortname (calculated)];
        "|"
    );
    CROSSFILTER ( Personnel[PNR]; PNR_Projects[PNR]; BOTH )
)

[SebastianAlmer]

I might have found some additional information: If I change the relation between Table "Personnel" and "PNR_Projects" to cross filter both, than it seems to work here. But can't do as it shredders some other visuals I need. Is there any option to concatenate text based on a filter or a changed relationship only for this visual with cross filter both?

[Greg_Deckler]

@SebastianAlmer - You should be able to create a measure that uses CONCATENATEX to concatenate all of the names together.

[SebastianAlmer]

@Greg_Deckler Thanks for your fast response. That leads to exakt this strange behaviour as you can see in the last screenshot. It puts together all names, not only those relevant for the specific project