Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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_Year | Business | Measureable | Type of Metric | Measure Description | Actuals | Target |
1/10/2024 0:00 | XYZ | Responsive | Operational | CXOne provide reporting on this metric | 75% | 100% |
2/10/2024 0:00 | XYZ | Responsive | Operational | Client Proposal Metric for calls | 85% | 75% |
3/10/2024 0:00 | ZZY | Expert | VoC | client Acceptance actuals | 90% | 100% |
Solved! Go to Solution.
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
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.
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.
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.
The measure description is repeated based on the months for different metric.
Thanks Raj, I did the two measures but what about the final one the condition.
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).
Proud to be a Super User! | |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
60 | |
51 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |