Have multiple filters in SUMX

[Anonymous]

I have a table named 'IT Help Desk' with a column 'TicketID'. There is also a column named 'ITOwnerID' for the people who solves the 'TicketID's'. Those IT owner's can manage multiple tickets. As last there's a calculated column which gives the status of the ticket based on the date closure. If the column 'Closure Date' is blank, than the ticket is still open. As you can see in the code below I want to have a count of the tickets that are still open and tickets that are closed.

I also have a table, see it as a dimension for the ITOwners but there's also a extra row named "Others". I work with a TopN and "Others" so when I select top 5 in my slicer I have the count of tickets open and closed for my top 5 but I also want the sum of the tickets open and closed for my "Others". In the code below you can see the total of tickets for the "Others" group but I want to get the TicketStatus = "Open" for the "Others group, but HOW?

Table 'IT Help Desk'

Table 'Owners'

Relationship

 

TicketsOpen = IF(
    RANKX(ALL('Owners'[ITOwnerID]); CALCULATE(COUNT('IT Help Desk'[TicketID]))) <= SELECTEDVALUE('TopN'[TopN]);
    CALCULATE(COUNT('IT Help Desk'[TicketStatus]); FILTER('IT Help Desk'; 'IT Help Desk'[TicketStatus] = "Open"));
    IF(
        HASONEVALUE(Owners[ITOwnerID]);
        IF(
            VALUES(Owners[ITOwnerID]) = "Others";
            SUMX(FILTER(ALL(Owners[ITOwnerID]); RANKX(ALL('Owners'[ITOwnerID]); CALCULATE(COUNT('IT Help Desk'[TicketID]))) > SELECTEDVALUE('TopN'[TopN])); CALCULATE(COUNT('IT Help Desk'[TicketID])))
        )
    )
)

Can somebody help me? Please?

[Anonymous]

Hi @Anonymous,

Can you please share a pbix file with part of some sample data for test and coding formula?

Regards,
Xiaoxin Sheng

[Anonymous]

Hello,

Of course.
I'm going to share the Power BI file with all the data. It doens't matter because it's sample data that I found on the Internet so it's not real data.

Is it good if I share the file via WeTransfer? Here is the link to it: https://we.tl/t-WihQ5HHWMX

In the measures 'TicketsClosed' and 'TicketsOpen' I'd really like to use those filters but if you have a better idea, shoot.

Kind regards

[Anonymous]

HI @Anonymous,

You can try to use following measures, I modify your formulas and replace 'all' functions(which will ignore filter effects) to 'allselected':

TicketsClosed = IF(
    RANKX(ALLSELECTED('Owners'[ITOwnerID]), CALCULATE(COUNT('IT Help Desk'[TicketID]))) <= SELECTEDVALUE('TopN'[TopN]),
    CALCULATE(COUNT('IT Help Desk'[TicketStatus]), FILTER('IT Help Desk', 'IT Help Desk'[TicketStatus] = "Closed")),
    IF(
        HASONEVALUE(Owners[ITOwnerID]),
        IF(
            VALUES(Owners[ITOwnerID]) = "Others",
            SUMX(FILTER(ALLSELECTED(Owners[ITOwnerID]), RANKX(ALLSELECTED('Owners'[ITOwnerID]), CALCULATE(COUNT('IT Help Desk'[TicketID]))) > SELECTEDVALUE('TopN'[TopN])), CALCULATE(COUNT('IT Help Desk'[TicketStatus])))
        )
    )
)

TicketsOpen = IF(
    RANKX(ALLSELECTED('Owners'[ITOwnerID]), CALCULATE(COUNT('IT Help Desk'[TicketID]))) <= SELECTEDVALUE('TopN'[TopN]),
    CALCULATE(COUNT('IT Help Desk'[TicketStatus]), FILTER('IT Help Desk', 'IT Help Desk'[TicketStatus] = "Open")),
    IF(
        HASONEVALUE(Owners[ITOwnerID]),
        IF(
            VALUES(Owners[ITOwnerID]) = "Others",
            SUMX(FILTER(ALL(Owners[ITOwnerID]), RANKX(ALLSELECTED('Owners'[ITOwnerID]), CALCULATE(COUNT('IT Help Desk'[TicketID]))) > SELECTEDVALUE('TopN'[TopN])), CALCULATE(COUNT('IT Help Desk'[TicketID])))
        )
    )
)

Regards,

Xiaoxin Sheng