Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
Solved! Go to 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 )
)
Hi @SebastianAlmer ,
Something like this:
CONCATENATED SHORT NAMES =
CALCULATE (
CONCATENATEX (
VALUES ( Personnel[Shortname (calculated)] );
Personnel[Shortname (calculated)];
"|"
);
CROSSFILTER ( Personnel[PNR]; PNR_Projects[PNR]; BOTH )
)
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?
@SebastianAlmer - You should be able to create a measure that uses CONCATENATEX to concatenate all of the names together.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |