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.
Hi All,
as you can see in the photo, I have a table of two columns.
StoreId: every value is repeated three times because every value has three TagId.
I'm trying to create a new column which concatenates every three rows in on row for each single StoreId.
I am looking for "for each" or "while" statements in Dax but till now no luck.
Thanks for help I would appreciate any Idea!
Regards,
Taher
Solved! Go to Solution.
Hi Taher,
If you want it in DAX, you could try these two options.
Option 1: there will be duplicate rows.
New = CALCULATE ( CONCATENATEX ( 'Table1', 'Table1'[TagId], "-" ), FILTER ( 'Table1', 'Table1'[StoreId] = EARLIER ( Table1[StoreId] ) ) )
Option 2: create a new table.
Table = SUMMARIZE ( 'Table1', 'Table1'[StoreId], "NewColumn", CONCATENATEX ( 'Table1', 'Table1'[TagId], "-" ) )
Best Regards!
Dale
Hi Taher,
Did you solve your problem? Could you please mark the proper answer if it's convenient for you? That will be a help to the others.
Best Regards!
Dale
Hi Taher,
If you want it in DAX, you could try these two options.
Option 1: there will be duplicate rows.
New = CALCULATE ( CONCATENATEX ( 'Table1', 'Table1'[TagId], "-" ), FILTER ( 'Table1', 'Table1'[StoreId] = EARLIER ( Table1[StoreId] ) ) )
Option 2: create a new table.
Table = SUMMARIZE ( 'Table1', 'Table1'[StoreId], "NewColumn", CONCATENATEX ( 'Table1', 'Table1'[TagId], "-" ) )
Best Regards!
Dale
Hi @v-jiascu-msft,
sorry, I was on holiday, so I have not tried it until today.
It did work with Summerize, thanks.
I would be interested if it could work without adding a new table, I mean is there an alternative option, which enables me doing this concatenating within my base table.
Thank u all 🙂
Taher
Hi Taher,
Did you try option 1? You don't need to create a new table with this option.
Best Regards!
Dale
Hi @v-jiascu-msft,
it is working thank you. But I am now in a new trouble.
I need to split the calculated column.
I can't see the new column or even the new table in the query editor. With M script I got a syntax error.
Best,
Taher
Hi,
It seems I've wrongly imagined that I need to concatenate the column.
I need to use the TagId as a filter in the report, the TagId itself doesn't be allowed to filter each other, because of that I thought the best way is to split them in one row so every StoreId can get its 3 TagId without any problem and then can I filter by TagId.
Any idea from you would be great 🙂
Hi Taher,
Why can't TagId filter each other? Could you please post a sample? It's a good idea to create a new thread if it's different with this topic. That would be great for others to search solutions in the community.
Best Regards!
Dale
Hello @v-jiascu-msft,
I'm sorry for not replying quickly. I'm writing exams for the university.
This is the full description of the data the report which I make.
From the table 'Tags ' I should get the TagID that tell me which StoreID ordered to which TagID.
Then the StoreID is connected to its info Table 'Stores ' which is to the main Table 'Answers' connected.
Now I am getting answers to questions from the main table.
What has TagID to do in my report? every TagID has a 'name' that is the person who sends me the answers and a ParentTagID which order the type of person (boss, normal employee, student employee) that's why for every store there are three Tags.
Now in the report, the user must filter the report due to the three Tags, because the Boss has many employees and the employee has many student employees.
so I choose the Boss then I get the employees according to this Boss and so on!
Hi @taher,
Maybe we could add a new column "boss" to the table "tags". Then the "tags" would be like the upper of the picture. Finally, it's easy to add slicers. Could you please post a little sample of table "tags"?
Best Regards!
Dale
Hello @v-jiascu-msft,
hier you find a full sample project.
ParentTagID = 150 =>Boss
ParentTagID = 140 =>Worker
ParentTagID = 130 =>Student.
https://www.4shared.com/s/fWHB5XsRDei
as you can see when I filter due to StoreName, the three Boss, worker and the student are appearing in the three slicers.
It should just on of them for each slicer according to its PaentTagID.
Thanks a lot 🙂
Hi @taher,
I can't download the file from your URL. The download button didn't work. Could you please use GoogleDrive or OneDrive?
Best Regards
Dale
Hi Taher,
First, I would recommend a custom slicer.
Add a calculated column first in the table "tags".
PName = IF ( [ParentTagID] = 130, "Student", IF ( [ParentTagID] = 140, "Worker", IF ( [ParentTagID] = 150, "Boss", "Null" ) ) )
Second, due to the measure can be put in a slicer, we have to filter these data to a new table.
130 = FILTER ( 'Tags', 'Tags'[ParentTagID] = 130 )
140 = FILTER ( 'Tags', 'Tags'[ParentTagID] = 140 )
150 = FILTER ( 'Tags', 'Tags'[ParentTagID] = 150 )
Best Regards!
Dale
hello @v-jiascu-msft,
we still have the same Problem.
when I for Example filter according to the worker 'Mark', it filters all the Bosses and the Students! But 'Mark' must filter just the ones who ist not in a pair with him in the original table ( Mark has John as a Boss and Andre as a student) these but disappeared too.
It is really difficult, I'v just tried and tried but it would not work.
Best,
Taher
If you want to concatenate them in 1 column, I'd suggest the following approach:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYHQjLCM4yBrOMgCwTOMsUzjJTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StoreID = _t, TagID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"StoreID", Int64.Type}, {"TagID", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"StoreID"}, {{"Concatenate", each Text.Combine(_[TagID], "-"), type table}}) in #"Grouped Rows"
You group on StoreID, select the TagID column as list and combine all those items (Text.Combine).
File to play around: https://1drv.ms/u/s!Av_aAl3fXRbehasUN2d7tmLElWqypQ
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF;
this code seems strange to me.
Can you please suggest me a tutorial for beginners to learn this type of coding.
Best,
Taher
Showing how to incorporate the code from above into your solution: http://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-co...
Some learning resources for M: http://www.thebiccountant.com/learning-resources/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@taher are you saying you want to concatenate all three tagid into one row and one column? i.e (1,2,3)
think you need to pivot for that in power query
Proud to be a Super User!
User | Count |
---|---|
89 | |
88 | |
84 | |
81 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |