LISTAGG for power BI with data from multiple tables

[OPS-MLTSD]

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 ID	Marks	Subject
12345	87, 98	Math, Biology
12678	90, 85, 88	Art, English, Math
12556	91, 89	Biology, Physics
12987	99, 90, 90	Art, 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

[VahidDM]

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:

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/

[OPS-MLTSD]

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."

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

[VahidDM]

@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/

[OPS-MLTSD]

@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 ID	Marks	Subject
	98,85	Biology, English
12345	87, 98	Math, Biology
12678	90, 85, 88	Art, English, Math
12556	91, 89	Biology, Physics
12987	99, 90, 90	Art, 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], "," )
)

[smpa01 · 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], "," )
)

[OPS-MLTSD]

@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 ID	Marks	Subject
12345	87, 98	Math, Biology
12678	90, 85, 88	Art, English, Math
12556	91, 89	Biology, Physics
12987	99, 90, 90	Art, 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!

[smpa01]

@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    |

Pbix is attached

[OPS-MLTSD]

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

[smpa01]

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

[OPS-MLTSD]

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

[Ashish_Mathur]

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.

[OPS-MLTSD]

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.