Dynamic slicer based on text

[Runnymede]

I have the following data in table "Departments":

Month    Qty    Tags
-------------------------
Sep      8      IT,HR
Oct      3      HR
Nov      4      IT,HR,PM

I have a second table "Tags", as follows:

Tags
-----
HR
IT
PM

I'm trying to represent the sum of Qty in a column chart, using a slicer populated by the rows in the Tags column. For example,

• Select "IT" in the slicer, the column in the chart would show: 12   (Sep's 8 + Nov's 4)
• Select "HR" in the slicer, the column would show: 15   (Sep's 8 + Oct's 3 + Nov's 4)
• Select "PM" in the slicer, the column would show: 4   (Nov's 4)
• Select "IT" and "HR" in the slicer, the column would show: 15   (Sep's 8 + Oct's 3 + Nov's 4)
• Select "IT" AND "PM" in the slicer, the column would show: 12  (Sep's 8 + Nov's 4)
• Select "HR" AND "PM" in the slicer, the column would show: 15   (Sep's 8 + Oct's 3 + Nov's 4)
• Select "IT" AND "HR" AND "PM" in the slicer, the column would show: 15   (Sep's 8 + Oct's 3 + Nov's 4)

This is a simplified representation.

I have no idea how to achieve this. Any help would be greatly appreciated.

Thanks.

[v-xicai]

Hi  @Runnymede ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not,  let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi @Runnymede ,

Firstly, you may enter into Query Editor, split the original Tags column, then Unpivot the result Tags columns ,remove Attribute column, rename the value column as Tags.

Secondly, you can create measure like DAX below, put the measure onto Card visual.

Sum Qty = var _table=SUMMARIZE(Departments,Departments[Month],"d",CALCULATE(DISTINCT(Departments[Qty]),FILTER(Departments,Departments[Month]=MAX(Departments[Month]))))
return SUMX(_table,[d])

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Greg_Deckler]

I am sure that I could code this up for a measure that you could use to include/exclude items in your visual. However, I feel like you would be better served if in Power Query you split out your last column by your comma delimiter and unpivoted the resulting columns. Then you could relate the two tables together and at that point what you are attempting to do becomes trivial with no code involved.