Forum Discussion

Massingc1983's avatar
Massingc1983
Frequent Visitor
2 years ago

Filtered DAX Measure

Hi   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"...
Dangar332's avatar
Dangar332
Resident Rockstar
2 years ago

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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • Massingc1983's avatar
      Massingc1983
      Frequent Visitor
      2 years ago

      Thank you, this helped!