cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OPS-MLTSD
Helper V
Helper V

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!






New Animated Dashboard: Sales Calendar


View solution in original post

12 REPLIES 12
VahidDM
Community Champion
Community Champion

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

 

VahidDM
Community Champion
Community Champion

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






New Animated Dashboard: Sales Calendar


View solution in original post

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






New Animated Dashboard: Sales Calendar


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!






New Animated Dashboard: Sales Calendar


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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors