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

New Member

## DISTINCTCOUNT of Rows filtered by other column

Trying to create a measure to use in a card.

 Field Time 1 0 1 2 1 2 1 1 2 0 2 -1 3 0 3 0 3 0

Distinctcount of row (Field) has to be filtered and not counted if it contains a time value of <0.

Trying to get an outcome like this.

 Sum of Distinct field with out negative time time value 2

Still new to PowerBI an DAX. Any help is appreciated! 🙂

1 ACCEPTED SOLUTION
Super User

@KamaClay , you can try this measure:

``````dcount =
VAR t =
FILTER (
VALUES ( 'Table'[Field] ),
"minv",
VAR current_field = CALCULATE ( SELECTEDVALUE ( 'Table'[Field] ) )
RETURN
CALCULATE ( MIN ( 'Table'[Time] ), 'Table'[Field] = current_field )
),
[minv] >= 0
)
RETURN
COUNTROWS ( t )``````

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

5 REPLIES 5
Community Champion

To calculate the distinct count of rows in the "Field" column, excluding those with a "Time" value less than 0, you can create a DAX measure. Here's how you can do it:

DistinctCountFiltered = VAR FilteredTable = FILTER('YourTableName', 'YourTableName'[Time] >= 0) RETURN DISTINCTCOUNTX(FilteredTable, 'YourTableName'[Field])

Replace 'YourTableName' with the actual name of your table.

This measure first filters the table to exclude rows where the "Time" column has a value less than 0. Then, it calculates the distinct count of the "Field" column based on the filtered table.

Once you create this measure, you can use it in a card visual to display the sum of distinct "Field" values without negative "Time" values.

Super User

@KamaClay , you can try this measure:

``````dcount =
VAR t =
FILTER (
VALUES ( 'Table'[Field] ),
"minv",
VAR current_field = CALCULATE ( SELECTEDVALUE ( 'Table'[Field] ) )
RETURN
CALCULATE ( MIN ( 'Table'[Time] ), 'Table'[Field] = current_field )
),
[minv] >= 0
)
RETURN
COUNTROWS ( t )``````

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

New Member

Hello @ERD ERD,

This solution does work for what I originally asked, but for my real purpose I need it to fit into a percentage calculation. I see I needed to be more specific, I apologize.

Adjusted % on Time = SUMX(VALUES('Master INV'[Field]),CALCULATE(MAX('Master INV'[Ratio for on time completion calculation]),'Master INV'[Alloted_Prep_Time]>-1)/(FILTER (
VALUES ( 'Master INV'[Field] ),
"minv",
VAR current_field = CALCULATE ( SELECTEDVALUE ( 'Master INV'[Field] ) )
RETURN
CALCULATE ( MIN ( 'Master INV'[Alloted_Prep_Time] ), 'Master INV'[Field] = current_field )
),
[minv] >= 0
)
RETURN
COUNTROWS(t)
*100))

The RETURN command does not play nicely. I hope this gives better idea to what I am trying to do. Thank you for your resolution, you infact did answer my original question, I failed to ask exactly what I needed.
Super User

I don't know what you're trying to calculate. Your example contains more columns. You need to provide a correct demo with the expected result.

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Super User

Hi @KamaClay ,

I've prepared your required output, but the answer is not 2 but is 3 as shown below:

I attach the link to the pbix file below:

DISTINCTCOUNT of Rows filtered by other column.pbix

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 - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors