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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Daniel_Derbin
Frequent Visitor

Filter while using ALLEXCEPT

Hi,

I have an issue with creating one DAX measure, hope some of you could help here.

The task is to show % spread of vulnerabilities but with aggregation of 100% per type of Severity.

Daniel_Derbin_0-1664182636095.png

 

Used measure:

% of vuln wo Sev =

 

    CALCULATE(

        IF(

            DIVIDE([Number of Vulnerabilities],[Sum of vulnerabilities wo sev],0) = BLANK(),

            0,

            DIVIDE([Number of Vulnerabilities],[Sum of vulnerabilities wo sev],0)

        )

    )

 

And underlying measures are:
Number of Vulnerabilities = count('TSCDB_Prod LIVE'[ID])

 

 

Sum of vulnerabilities wo sev =

 

    CALCULATE(

        [Number of Vulnerabilities],

        ALLEXCEPT('TSCDB_Prod LIVE','TSCDB_Prod LIVE'[Severity]),

        'TSCDB_Prod LIVE'[Closed status] = "Open"

    )

 

 

As you see this measure works as intended but issue appears when I try to add organization dimension as a filter:

Without ORG filter:

 

Daniel_Derbin_1-1664182636103.png

 

 

With applied filter Number of Vulnerabilities changes but Sum of Vuln wo SEV remains the same and that affects % of Vuln wo Sev as it should still calculate 100% based on selection.

 

Daniel_Derbin_2-1664182636108.png

 

 

Any idea how to get around issue to have Sum of vuln wo Sev be able to react to filtering?

 

 

2 REPLIES 2
Anonymous
Not applicable

Hi @Daniel_Derbin 

Please try this measure :

Sum of vulnerabilities wo sev =
 CALCULATE(
        [Number of Vulnerabilities],  
        FILTER('TSCDB_Prod LIVE',[Severity]=MAX('TSCDB_Prod LIVE'[Severity]) &&
         'TSCDB_Prod LIVE'[Closed status] = "Open"
    ))

Because you are using allexcept here , the filter in slicer does not work for the return value of this measure .

ALLEXCEPT function (DAX) - DAX | Microsoft Learn 

 

Best Regards,
Community Support Team _ Ailsa Tao

HI Ailsa,

Thank you for your feedback, it didn't do the trick sadly.
Measure is now filtering through slicer but gives same result as Number of Vuln.



Daniel_Derbin_0-1664267331889.png

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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