Filter while using ALLEXCEPT

[Daniel_Derbin]

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.

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:

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.

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

[Anonymous]

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

[Daniel_Derbin]

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.