Mark records with multiple keys

[lg01]

I have a massive table with transactions. These transactions are in a wide table, with many columns, and marked with a series of tags that become relevant for accounting purposes. Now, the tags related to the transaction are basically concatenated in a column instead of a skinny table repeating the transaction multiple times with different tags. So my transaction table looks like this:

TransactionID	TagCode
Trans001	Tag9/Tag3/Tag10
Trans002	Tag1/Tag6/Tag8/Tag20/Tag11/Tag17
Trans003	Tag3/Tag1/Tag15/Tag7

I also have a table with two columns with the Tag code and the tag description that looks like this:

TagCode	TagDescription
Tag1	Tag Description 1
Tag2	Tag Description 2
Tag3	Tag Description 3
Tag4	Tag Description 4
Tag5	Tag Description 5
Tag6	Tag Description 6
Tag7	Tag Description 7
...
Tag25	Tag Description 25

What I'd like to do is to have the list of tags in a simple chart table, where clients can select the tag they are interested in and the transactions related to that Tag will show up.  On my first pass, I tried pivoting the transaction table and breaking the tags into rows; but since the table is so massive, it is too slow to do. I also tried Adding the tags into columns; but run into a similar issue because some transactions have many tags and I end up with a large number of columns. So, what I'd like to do is to have some type of look up that can find the related transactions. Is that possible? What would be the syntax?

[V-yubandi-msft]

Hi @lg01 ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

[V-yubandi-msft]

Hi @lg01 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @lg01 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you  or let us know if you need any further assistance?

Your feedback is important to us, Looking forward to your response. 

[V-yubandi-msft]

Hi @lg01 ,

The approach suggested by the @bhanu_gautam , is good  breaking tag strings into rows and using a distinct tag list with a slicer is an effective way to manage this situation.

It should scale much better compared to pivoting wide or creating multiple columns, especially with large datasets.

If you haven't tried it yet, it's a good idea to first test it on a smaller portion of your data. This way, you can verify its performance and accuracy before applying it to your full model.

If you need any more clarification, please feel free to ask. Otherwise, if your issue is resolved, marking it as the accepted solution will help other community members facing a similar challenge.

[bhanu_gautam · Accepted solution]

@lg01 Load your transaction table into Power BI.
Go to the Power Query Editor.
Select the TagCode column.
Use the Split Column feature to split the TagCode column by delimiter /.
Unpivot the resulting columns to create a table where each tag is in a separate row.

Load your tag description table into Power BI.
Create a relationship between the split tags table and the tag description table based on the TagCode.

Create a new table in Power BI using DAX to list all unique tags:

UniqueTags = DISTINCT('SplitTagsTable'[TagCode])

Create a relationship between the UniqueTags table and the SplitTagsTable based on the TagCode.

Create a measure to filter transactions based on the selected tag:

DAX
FilteredTransactions =
CALCULATE(
COUNTROWS('TransactionsTable'),
FILTER(
'SplitTagsTable',
'SplitTagsTable'[TagCode] IN VALUES('UniqueTags'[TagCode])
)
)

Use the UniqueTags table to create a slicer in your report.
Use the FilteredTransactions measure to create a table or any other visual to display the filtered transactions.