topic Re: Filtered DAX Measure in DAX Commands and Tips

Filtered DAX Measure

Massingc1983 — Fri, 13 Oct 2023 16:35:19 GMT

I am trying to work out a percentage based on filtered measures. The first measure is number of "Active" suppliers. The second is number of those active suppliers who are marked as "High Risk" in another column. Then i would like to divide the two results "Active Suppliers High Risk" by "Active Suppliers" to give a percentage.

I have come up witht he below formula however it gives me 100% and i am not sure where i am going wrong. Any help would be appreciated.

Active Supplier ABC =

VAR ActiveSuppliers =

CALCULATE(

DISTINCTCOUNT('Contract Append'[Supplier name]),

'Contract Append'[Status] = "Active" )

VAR ABCHighRiskSuppliers =

CALCULATE(ActiveSuppliers, 'Contract Append'[2.2 Modern Slavery Risk Rating] = "High")

Return

DIVIDE(ABCHighRiskSuppliers, ActiveSuppliers)

Re: Filtered DAX Measure

Dangar332 — Sat, 14 Oct 2023 07:20:13 GMT

hi, Massingc1983
try below

Active Supplier ABC =

VAR ActiveSuppliers =

CALCULATE(

DISTINCTCOUNT('Contract Append'[Supplier name]),

'Contract Append'[Status] = "Active" )

VAR ABCHighRiskSuppliers =

CALCULATE(

DISTINCTCOUNT(

'Contract Append'[Supplier name]), 'Contract Append'[2.2 Modern Slavery Risk Rating] = "High",

KEEPFILTERS('Contract Append'[Status] = "Active"))

)

Return

DIVIDE(ABCHighRiskSuppliers, ActiveSuppliers)

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly. Appreciate your kudos.

Re: Filtered DAX Measure

Anonymous — Tue, 17 Oct 2023 06:49:07 GMT

Hi Massingc1983 ,

I don't suggest you to use "ActiveSuppliers" part in "ABCHighRiskSuppliers", use DISTINCTCOUNT() function again in "ABCHighRiskSuppliers".

Active Supplier ABC = VAR _ActiveSuppliers = CALCULATE ( DISTINCTCOUNT ( 'Contract Append'[Supplier name] ), 'Contract Append'[Status] = "Active" ) VAR _ABCHighRiskSuppliers = CALCULATE ( DISTINCTCOUNT ( 'Contract Append'[Supplier name] ), 'Contract Append'[Status] = "Active", 'Contract Append'[2.2 Modern Slavery Risk Rating] = "High" ) RETURN DIVIDE ( _ABCHighRiskSuppliers, _ActiveSuppliers )

It works on myside. My Sample:

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Filtered DAX Measure

Massingc1983 — Thu, 19 Oct 2023 08:13:34 GMT

Thank you, this worked!

Re: Filtered DAX Measure

Massingc1983 — Thu, 19 Oct 2023 08:13:50 GMT

Thank you, this helped!