topic Re: DISTINCTCOUNT of Rows filtered by other column in DAX Commands and Tips

DISTINCTCOUNT of Rows filtered by other column

Anonymous — Thu, 14 Sep 2023 22:43:28 GMT

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

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

Re: DISTINCTCOUNT of Rows filtered by other column

DataNinja777 — Fri, 15 Sep 2023 02:56:30 GMT

Hi Anonymous ,

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

Re: DISTINCTCOUNT of Rows filtered by other column

ERD — Fri, 15 Sep 2023 06:57:33 GMT

Anonymous , 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 )

Re: DISTINCTCOUNT of Rows filtered by other column

Anonymous — Fri, 15 Sep 2023 21:06:05 GMT

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.

Re: DISTINCTCOUNT of Rows filtered by other column

ERD — Sat, 16 Sep 2023 14:01:40 GMT

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.

Re: DISTINCTCOUNT of Rows filtered by other column

123abc — Mon, 18 Sep 2023 12:13:10 GMT

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.