DISTINCTCOUNT of Rows filtered by other column

[KamaClay]

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 t ime time value

2

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

[123abc]

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.

[ERD · Accepted solution]

@KamaClay , you can try this measure:

dcount =
VAR t =
    FILTER (
        ADDCOLUMNS (
            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 )

[KamaClay]

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 (

        ADDCOLUMNS (

            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.

[ERD]

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.

[DataNinja777]

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