Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to 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], "," )
)
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/
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
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/
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], "," )
)
@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], "," )
)
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!
@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
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
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.
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.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
92 | |
92 | |
84 | |
80 | |
49 |
User | Count |
---|---|
146 | |
137 | |
109 | |
68 | |
55 |