Forum Discussion
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.
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?
2 Replies
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 TaoHI 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.
