Many to many - tickets with multiple tags. CALCULATE - dynamic filter context intersection

[YuriyM]

Hi Community, 

Here is my data model 

Tickets-*Bridge*-Tags

I need to filter (or basically distinct count) a set of tickets that are labeled with a set of tags. 

For example  - tags I may be interested in might be "help" and "power bi" and I need all tickets that have them both. 

I know I can do something like

_ticketsCount = CALCULATE(DISTINCTCOUNT('Tickets'[TicketID]), CALCULATETABLE('Tickets','Tags'[TagName]= "help"),CALCULATETABLE('Tickets','Tags'[TagName]= "power bi"))

my goal is to make the selection of tags somehow interactive, so I add a table with tags of interest and my formula becomes

_ticketsCountInteractive = CALCULATE(
DISTINCTCOUNT('Tickets'[TicketId]),
CALCULATETABLE('Tickets',CONTAINSROW(ALLSELECTED(TagsOfInterest[TagName]),'Tags'[TagName]))
)

 my problem is  - calculatetable returns all tickets that have at least one tag (from the selected from tags of interest table), but not only tickets that have both tags on them..

any suggestions? is that achievable ? should I maybe look at it from another angle?

any thoughts are highly appreciated.

thanks

[amitchandak]

@YuriyM , Create an inactive many to many join between ticket and tags.

The use then when you want to filter tags

example

_ticketsCount = CALCULATE(DISTINCTCOUNT('Tickets'[TicketID]), Filter('Tickets','Tags'[TagName] in{ "help", "power bi"}) , userelation('Tickets'[ID],'Tags'[TicketID]))

or

_ticketsCount = CALCULATE(DISTINCTCOUNT('Tickets'[TicketID]), userelation('Tickets'[ID],'Tags'[TicketID]))

[Anonymous]

// The bridge table does contain
// TickeID and TagID and that's
// enough to calculate what you want.
// Let's say that you select several
// tag names from the Tags table...

[# Tickets With Selected Tags] =
var __numOfTags = COUNTROWS( Tags )
var __ticketsWithNumOfTags =
    COUNTROWS(
        FILTER(
            DISTINCT( Bridge[TicketID] ),
            CALCULATE(
                COUNTROWS( Bridge ) = __numOfTags
            )
        )
    )
return
    __ticketsWithNumOfTags

[YuriyM · Accepted solution]

Thanks for the answer @Anonymous .

Looks similar to what I ended up with. I had to add a group be/having analogue to make this work.

It works, but you can not break that down by tag (not my need though) for obvious reasons.

_m3 = CALCULATE(DISTINCTCOUNT(Bridge[TicketId]),
            filter(
                    SUMMARIZE(
                    filter(
                        bridge
                        ,related(Tags[TagName]) in ALLSELECTED(TagsOfInterest[TagName]))
                    ,Bridge[TicketId]
                    ,"rowcount",COUNT(Bridge[TagId]))
            ,[rowcount] = countrows(ALLSELECTED(TagsOfInterest[TagName]))))