Forum Discussion
Aggregate string into groupings based on filter selection
Hi,
I've perused some of the FAQs but can't seem to find a topic on the issue below.
I have a data table with a multi variate field saved under a text column (Diagnosis). The front end is a multi select field and appears to capture each selection seperated by a semi colon seen in the dataset. I'm working towards building a slicer after entering a keyword in the search box, eg sickle, would sum the views group by code so below would ideally group RRU to Sickle cell disease with 152 views (70+54+28).
The slicer used in the screenshot is called Text Search which returns all permutations in the string but I need it to go another step towards grouping keywords on the go.
Is there a solution close to this or even having the user to enter the exact string (eg sickle cell disease) would be a great improvement already.
Hope to hear back soon. Thanking you in advance.
Hi jos818
In the query editor, I would add an index column which will serve as a reference to the original rows prior to adding a column that splits diagnosis into several rows for each semicolon
The steps above will result to the table below.
Notice that index 11 repeats 3x so the measure to total the views should take this into consideration.
Total Views = SUMX ( VALUES ( 'Table'[Index] ), CALCULATE ( MAX ( 'Table'[Daily views] ) ) )Please see the attached pbix.
6 Replies
jos818 You can try to Split column and create a new separate table
Split the Diagnosis Column: First, you need to split the Diagnosis column into individual diagnoses. This can be done using Power Query.
Create a Relationship: Create a relationship between the split diagnoses and the original table.
Create a Measure for Summing Views: Create a measure that sums the Daily views based on the selected keyword.
Create a Slicer: Use the new column with individual diagnoses as a slicer.
Hi jos818
In the query editor, I would add an index column which will serve as a reference to the original rows prior to adding a column that splits diagnosis into several rows for each semicolon
The steps above will result to the table below.
Notice that index 11 repeats 3x so the measure to total the views should take this into consideration.
Total Views = SUMX ( VALUES ( 'Table'[Index] ), CALCULATE ( MAX ( 'Table'[Daily views] ) ) )Please see the attached pbix.
Hi jos818,
Thanks for reaching out to the Microsoft fabric community forum.
This is a common challenge when dealing with multi select fields stored as delimited texts. As danextian and
bhanu_gautam both responded to your query, kindly go through their response and check if your issue can be resolved.
I would also take a moment to thank danextian and bhanu_gautam, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.Hi jos818,
As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well.
And if you're still looking for guidance, feel free to give us an update, we’re here for you.
Best Regards,
Hammad.Hi jos818,
Hope everything’s going smoothly on your end. As we haven’t heard back from you, so I wanted to check if the issue got sorted.
Still stuck? No worries just drop us a message and we can jump back in on the issue.Best Regards,
Hammad.
