Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
reddevil
Helper I
Helper I

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 Measure description from the table below, please help

 

Month_YearBusinessMeasureableType of MetricMeasure DescriptionActualsTarget 
1/10/2024 0:00XYZResponsiveOperationalCXOne provide reporting on this metric75%100%
2/10/2024 0:00XYZResponsiveOperationalClient Proposal Metric for calls85%75%
3/10/2024 0:00ZZYExpertVoCclient Acceptance actuals90%100%
1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

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 

 

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Does this measure work?

=countrows(filter(values(Data[Measure description]),[Actuals]>[Target]))

If not, then share the download link of the PBI file.  Show the expected result there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Bibiano_Geraldo
Super User
Super User

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.

 

 

Is this post help you? Please consider to:

Accept as Solution!
Give a Kudo
Follow me on Linkedin

VahidDM
Super User
Super User

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 

 

Thanks Vahid, the measure is working, appreciate your help. 

Thanks everyone for having a crack at this issue. Appreciated.

reddevil
Helper I
Helper I

The measure description is repeated based on the months for different metric.

reddevil
Helper I
Helper I

Thanks Raj, I did the two measures but what about the final one the condition.

rajendraongole1
Super User
Super User

Hi @reddevil - Create a measure calculates the distinct count of rows where the Actuals value is greater than or equal to the Target, grouped by Measureable and Type of Metric.

 

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

 

create measure calculates the distinct count of Measure Description across the table.

 

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

 

Distinct Count of Actuals >= Target: 1 (Row 2 only satisfies this condition).Distinct Count of Measure Description: 3 (Each row has a unique Measure Description).





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.