Forum Discussion

reddevil's avatar
reddevil
Icon for Helper II rankHelper II
1 year ago
Solved

Distinct count based on 3 criterias

Hi  I have a senario where i have to calculate the distinct count of Distinct count of Actuals >= Target (Based on Measurable being same, Type of Metric being same) and Distinct count of Total Measu...
General Comment
Need Help
  • VahidDM's avatar
    VahidDM
    1 year ago

    Hi reddevil 

    Use two separate measures. One to find the distinct count of Measure Descriptions where Actuals ≥ Target, and another to find the distinct count of all Measure Descriptions.

    Measure 1 (Actuals ≥ Target):

     

    DistinctCount_ActualsGreaterEqualTarget =
CALCULATE(
    DISTINCTCOUNT('Table'[Measure Description]),
    FILTER(
        ALL('Table'),
        'Table'[Actuals] >= 'Table'[Target]
    )
)

    Measure 2 (All Measure Descriptions):

    DistinctCount_MeasureDescription =
DISTINCTCOUNT('Table'[Measure Description])

     

    Adjust the table and column names as necessary. These measures give you the distinct counts as required.

     

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

    Appreciate your Kudos!! 

     

    LinkedIn|Twitter|Blog |YouTube 

     

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Icon for Super User rankSuper User
1 year ago

Hi reddevil ,

Please create the following measure:

Distinct Count of Actuals >= Target =
COUNTROWS(
    FILTER(
        SUMMARIZE(
            YourTable,
            YourTable[Measureable],
            YourTable[Type of Metric],
            YourTable[Measure Description],
            "Actuals Greater or Equal Target",
            MAX(YourTable[Actuals]) >= MAX(YourTable[Target])
        ),
        [Actuals Greater or Equal Target] = TRUE()
    )
)

 

Now you can create this DAX:

Distinct Count of Total Measure Description =
DISTINCTCOUNT(YourTable[Measure Description])

 

Let me know if it working.