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.
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.
Solved! Go to 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!
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")
Hi @pgolbi,
I have implemented this requirement in a PBI File. Please refer it for your purpose
Click here to get that file
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.
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!
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
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |