Reply
Frequent Visitor
Posts: 5
Registered: ‎12-17-2018

Have multiple filters in SUMX

[ Edited ]

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'

IT Help Desk.PNG

Table 'Owners'

Owners.PNG

Relationship

 Relationship.PNG

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?

Community Support Team
Posts: 7,714
Registered: ‎08-14-2016

Re: Have multiple filters in SUMX

Hi @KevinN,

 

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

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Frequent Visitor
Posts: 5
Registered: ‎12-17-2018

Re: Have multiple filters in SUMX

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

Community Support Team
Posts: 7,714
Registered: ‎08-14-2016

Re: Have multiple filters in SUMX

HI @KevinN,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |