topic Re: Filter while using ALLEXCEPT in DAX Commands and Tips

Filter while using ALLEXCEPT

Daniel_Derbin — Mon, 26 Sep 2022 08:58:08 GMT

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(),

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?

Re: Filter while using ALLEXCEPT

Anonymous — Tue, 27 Sep 2022 05:28:29 GMT

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

Re: Filter while using ALLEXCEPT

Daniel_Derbin — Tue, 27 Sep 2022 08:29:04 GMT

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.