topic Re: Filters don't return the correct count in DAX Commands and Tips

Filters don't return the correct count

ZakariAK — Tue, 05 Dec 2023 17:46:11 GMT

Im calculating some total values doing a count and filtering the rows with duplicate values in one column (Id Cliente)

When calculating the total without filters the row count is correct, but when adding filters i get wrong values.

When doing the count without filtering out any values I receive the correct number of rows: 4844

But when applying filters to filter out row that contain a certain value i receive the wrong count (80 values more than what it should)

This is what i should receive: 3513

Its clear its a problem with the filters but i don't grasp what its not adding up, can someone help me or give me the correct DAX measure?

Thanks in advance.

ariba_m — Wed, 06 Dec 2023 07:32:41 GMT

Hi there,

As per my understanding of the problem, you are trying to get a count of distinct values based on a column.

Try this measure:

For count of distinct values:

MeasureName = COUNTROWS(ALL([TableName][ColumnName]))

Example: Measure = COUNTROWS(ALL(Product_Table[return_date]))

Output:

Hope this helps!

ZakariAK — Thu, 07 Dec 2023 08:48:47 GMT

Hi,

The count I made is already returning it correct, the problem I have is when filtering out some rows depending of its value in a column.

ZakariAK — Thu, 07 Dec 2023 15:13:44 GMT

Hi,

The count I made is already returning it correct, the problem I have is when filtering out some rows depending of its value in a column.

'''Total Cupones =

CALCULATE(

COUNTROWS(

SUMMARIZE(

CRM,

CRM[Id Cliente]

)

NOT(

CRM[Promocion Periodo Cupon] IN {"OPPRCC - Petición reabrir cupón", "RPRCUP - Recupón", "RPRCUP - Recup��n"}

|| CRM[Motivo de cierre] IN {"G - Gestión Solicitud", "Gestión de solicitud"}

)

)'''