filter context intersection
1 Topic
Many to many - tickets with multiple tags. CALCULATE - dynamic filter context intersection
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. thanksSolved1.1KViews0likes3Comments