cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Measure distinct count

Hi all,

I am trying to formulate a measure that counts the id's that are in the status 'build_offer'.

However I only want to count the id's where the status build_offer is not finished yet but the other tasks are.

So in the example below I want to have a count of 2.

Could anyone help me with the formula

1 ACCEPTED SOLUTION
Community Support

You can create this measure:

``````Count =
VAR tab =
ALL ( 'Table' ),
"_count",
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[process_id] = EARLIER ( 'Table'[process_id] )
)
)
)
RETURN
COUNTX ( FILTER ( tab, [_count] < 2 ), [_count] )``````

Best Regards,
Yingjie Li

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

4 REPLIES 4
Community Support

You can create this measure:

``````Count =
VAR tab =
ALL ( 'Table' ),
"_count",
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[process_id] = EARLIER ( 'Table'[process_id] )
)
)
)
RETURN
COUNTX ( FILTER ( tab, [_count] < 2 ), [_count] )``````

Best Regards,
Yingjie Li

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

Helper I

@v-yingjl  Thank you for your help!

Super User

Can you explain why the count is 2 please?  I don't see any finished_on values

Helper I

The count is in this example two because id 4 & 5 have the task 'build_offer' that is not finished yet.

The other tasks have also another task that is not finished yet. That is why I don't want to count these id's.

In this example no tasks are finished, but in my full dataset off course there is.

So id's 4 & 5 have the task 'Plan_survey' that is already finished.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors