Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KevinN
Frequent Visitor

Have multiple filters in SUMX

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?

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.