Concatenate results when linking tables

[pgolbi]

There are 2 tables currently linked by many to one relationship.

Table A
RecordID

RecordDetails

Table B

ID

RecordID

DescriptionText

They are linked by RecordID A:B (one:many). A record in table A can have none, one or multiple rows in Table B.

The user wants the report showing table below:

RecordID, RecordDetails, DescriptionText

DescriptionText should be displayed as

1. "Nil" if no records found in Table B 

2. DescriptionText for a single record match

3. Concatenated (New line in the table cell) DescriptionText from all records for multiple records match

What is the best solution for this? I realise Power BI is not the best tool for this, with SSRS it would be a piece of cake while DAX is not my strongest skill :-).

Thank you very much for assistance.

[DaFloDo]

using DAX you could try adding a column like this (maybe you have to exchange semicolons by commata):

CombinedDescriptionColumn = 
var relatedEntriesCount = CALCULATE(COUNTA('Table B'[ID]);ALLEXCEPT('Table A';'Table A'[RecordID]))

return switch(
    True();
    relatedEntriesCount = 1; LOOKUPVALUE('Table B'[DescriptionText];'Table B'[RecordID];'Table A'[RecordID]); 
    relatedEntriesCount > 1; Calculate(CONCATENATEX('Table B';'Table B'[DescriptionText]; UNICHAR(10)); ALLEXCEPT('Table A';'Table A'[RecordID]));
    "NIL")

[pgolbi]

Hi @DaFloDo

Thank you. I will give it a go and let you know.

[Thejeswar]

Hi @pgolbi,

I have implemented this requirement in a PBI File. Please refer it for your purpose

Click here to get that file

[pgolbi]

Hi @Thejeswar

It does not look right. I have 2 tabkle linked one to many relationships. Also I expect a new measure/calculated field with something like CONCATENATEX or other DAX function.

Here is what I mean.

Table A

1, Record 1

2, Record 2

3, Record 3

Table B

1, 1, Description 1

2, 1, Description 2

3, 3, Description 3

Output should look like this:

1, Record 1, Description 1 + Description 2 (2 rows matching)

2, Record 2, Nil (0 rows matching)

3. Record 1, Description 3 (1 row matching)

Hope this makes sense.

[pgolbi · Accepted solution]

Hi @DaFloDo,

There were a couple of issues in your solution but it worked in general.

I used this https://community.powerbi.com/t5/Desktop/Count-in-two-related-tables/m-p/108319#M45532 to calculate relatedEntriesCount variable (including non matching)

CombinedDescriptions =
 var relatedEntriesCount = Calculate(IF(ISBLANK(COUNTROWS(RELATEDTABLE('Table B'))),0,COUNTROWS(RELATEDTABLE('Table B'))))

return switch(
    True(),
    relatedEntriesCount >= 1, Calculate(CONCATENATEX('Table B','Table B'[DescriptionText], UNICHAR(10)), ALLEXCEPT(Table A,Table A[IncidentID])),
    "NIL")

Thank you!

[v-frfei-msft]

Hi @pgolbi,

As your formula, it does work well. Kindly mark your answer as solution to close the case please! Thanks in advance.

Regards,

Frank