cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
YuriyM
Frequent Visitor

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.

thanks

1 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]))))

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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
Not applicable

// 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

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]))))

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors