Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
OPS-MLTSD
Post Patron
Post Patron

LISTAGG for power BI with data from multiple tables

Hello,

 

I have three tables: Students (main table), Marks, Subject. I created a relationship between the 3 tables using Student ID. Now, I am trying to create a table visual in my report that looks like this:

 

Student IDMarksSubject
1234587, 98Math, Biology 
1267890, 85, 88Art, English, Math
1255691, 89Biology, Physics
1298799, 90, 90Art, Math, English 

 

I saw that this person used CONCATENEX in this post: Solved: DAX Studio: Group by/Concatenate(Textcombine, list... - Microsoft Power BI Community

 

And I tried to follow their instructions but it did not work for me. Here is the calculated column that I tried to create in my Students table:

 

Measure = 
SUMMARIZECOLUMNS (
Students[Student ID],
Marks[Marks],
"Subject", CONCATENATEX ( VALUES ( Subject[Subject]), Subject[Subject], "," )
)

 

note: I have seen tutorials where people have used the group by function in power query, however, I cannot detele that and it would be much better to create the column using DAX

1 ACCEPTED SOLUTION

@OPS-MLTSD  does this work

 

Table 2 =
SUMMARIZECOLUMNS (
    Students[Student ID],
    TREATAS ( VALUES ( Students[Student ID] ), Marks[Student ID] ),
    TREATAS ( VALUES ( Students[Student ID] ), Subject[Student ID] ),
    "Marks", CONCATENATEX ( VALUES ( Marks[Marks] ), Marks[Marks], "," ),
    "Subject", CONCATENATEX ( VALUES ( Subject[Subject] ), Subject[Subject], "," )
)

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

12 REPLIES 12
VahidDM
Super User
Super User

Hi @OPS-MLTSD 

 

Try this code to create a new Table with Dax:

Table = 
SUMMARIZECOLUMNS (
Students[Student ID],
"Marks",CONCATENATEX ( VALUES (Marks[Marks]), Marks[Marks], "," ),
"Subject", CONCATENATEX ( VALUES ( Subject[Subject]), Subject[Subject], "," )
)

 

Output:

VahidDM_0-1637887302500.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

hello @VahidDM 

 

I tried to create this measure in my Students table and this is the error message I got: 

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

OPSMLTSD_0-1637891015155.png

Do I have to create a separate table for this? Can't I make this measure in my Students table? Thank you

 

@OPS-MLTSD 

 

Yes, You need to create another table with that dax code.

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

 

@VahidDM 

thanks, I was able create a new table with this DAX and everything seesm fine but there seems to be a NULL value in my Student ID column. I have checked my source tables and Student ID is not null in those source tables. My new table looks like this:

 

 

Student IDMarksSubject
 98,85Biology, English
1234587, 98Math, Biology 
1267890, 85, 88Art, English, Math
1255691, 89Biology, Physics
1298799, 90, 90Art, Math, English 

 

is there a way for me to remove this top row with the blank student ID? Can I do something like this to my DAX?:

 

 

Table = 
SUMMARIZECOLUMNS (
Students[Student ID] IS NOT BLANK,
"Marks",CONCATENATEX ( VALUES (Marks[Marks]), Marks[Marks], "," ),
"Subject", CONCATENATEX ( VALUES ( Subject[Subject]), Subject[Subject], "," )
)

 

 

@OPS-MLTSD  does this work

 

Table 2 =
SUMMARIZECOLUMNS (
    Students[Student ID],
    TREATAS ( VALUES ( Students[Student ID] ), Marks[Student ID] ),
    TREATAS ( VALUES ( Students[Student ID] ), Subject[Student ID] ),
    "Marks", CONCATENATEX ( VALUES ( Marks[Marks] ), Marks[Marks], "," ),
    "Subject", CONCATENATEX ( VALUES ( Subject[Subject] ), Subject[Subject], "," )
)

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@smpa01 

thanks, the problem is, because I have more subjects than marks, the dax formula you have above got rid of those subjects and only kept subjects for which the marks are available, I don't want to get rid of the subjects. I want all the subjects to show still even if the marks for those subjects don't exist yet. I want the data to look like this:

 

 

Student IDMarksSubject
1234587, 98Math, Biology 
1267890, 85, 88Art, English, Math
1255691, 89Biology, Physics
1298799, 90, 90Art, Math, English 
12399 Gym, Social Science

 

its okay to have NULL Marks or Subject but its not okay to have null Student ID. I hope that makes sense. Thank you to both for your help!

@OPS-MLTSD  it is still doing the job

| Students   |
|------------|
| Student ID |
|------------|
| 12345      |
| 12678      |
| 12556      |
| 12987      |
| 12399      |


|         Subject             |
|------------|----------------|
| Student ID | Subject        |
|------------|----------------|
| 114457     | Biology        |
| 114578     | English        |
| 12345      | Math           |
| 12345      | Biology        |
| 12678      | Art            |
| 12678      | English        |
| 12678      | Math           |
| 12556      | Biology        |
| 12556      | Physics        |
| 12987      | Art            |
| 12987      | Math           |
| 12987      | English        |
| 12399      | Gym            |
| 12399      | Art            |
| 12399      | Social Science |


|        Marks       |
|------------|-------|
| Student ID | Marks |
|------------|-------|
| 112233     | 98    |
| 112234     | 85    |
| 12345      | 87    |
| 12345      | 98    |
| 12678      | 90    |
| 12678      | 85    |
| 12678      | 88    |
| 12556      | 91    |
| 12556      | 89    |
| 12987      | 99    |
| 12987      | 90    |
| 12987      | 90    |

 

smpa01_0-1637956414014.png

 

Pbix is attached

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

that's strange in my file, I have a blank Student ID row but when I do this, the problem gets resolved:

 

Table 2 =
SUMMARIZECOLUMNS (
    Students[Student ID],
    --TREATAS ( VALUES ( Students[Student ID] ), Marks[Student ID] ),
    TREATAS ( VALUES ( Students[Student ID] ), Subject[Student ID] ),
    "Marks", CONCATENATEX ( VALUES ( Marks[Marks] ), Marks[Marks], "," ),
    "Subject", CONCATENATEX ( VALUES ( Subject[Subject] ), Subject[Subject], "," )
)

 

do you know why that would be the case?

 

ps: I really appreciate your and @VahidDM  help. I have accepted your answer as the solution. thank you

@OPS-MLTSD  if you post the pbix I can take a look

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Tbh I have some sensitive client level data here that I cannot post, is there anything else I can provide?

Hi,

Share some dummy data in all 3 tables and show the expected result.  Share data in a format that can be pasted in an MS Excel workbook.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I think there may have been an issue with one of the tables in my db that caused the NULL issue I described at the top. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.