NEED HELP // ERROR AL CONTAR

[Mikaseido1]

Buenas noches, 

Estoy tratando de contar la columna con nombre numIdentificacionHogar, la cual contiene valores repetidos y se debe contar lo que no se repita.

Existe otra columna con nombre nombreTerritorio, que contiene una enúmeración de T1 a T12. Requiero contar en una medida independiente y sola todos los hogares de todos los territorios.

Al realizar una tabla con los datos, arroja el resultado del conteo de los hogares sin contar los repetidos, pero lo curioso es que el TOTAL al final no suma la realidad de lo que cuenta.

Ejemplo valores

nombreTerritorio	numIdentificacionHogar
T02	H1919
T02	H1919
T02	H1919
T02	H1920
T02	H0408
T02	H0407
T02	H0406
T02	H0409
T02	H0410
T02	H0412
T02	H0413
T02	H0414
T02	H0411
T03	H1502
T03	H1501
T03	H1501
T03	H1502
T03	H1503
T03	H1503
T03	H1503
T03	H1001
T03	H1002
T03	H1002

Conteo esperado

nombreTerritorio	conteoHogares
T02	11
T03	5
Total	16

Requiero de su ayuda en validar como seria la medida para calcular la cantidad de hogares.

Gracias.

[v-pnaroju-msft]

Hi Mikaseido1,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Best Regards,

Pavan

[v-pnaroju-msft]

Hi Mikaseido1,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Best regards,
Pavan

[v-pnaroju-msft]

Hi @Mikaseido1,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Best regards,
Pavan

[v-pnaroju-msft · Accepted solution]

Hi @Mikaseido1,

Thank you for reaching out to us through the Microsoft Fabric Community Forum.

In addition to the response provided by @Omid_Motamedise  and @wini_R , please find below the solution using DAX to accurately count the total number of distinct households across all territories.To achieve this, we can create a measure using the DISTINCTCOUNT function.

The DAX measure is as follows:

CountHouseholdsByTerritory = DISTINCTCOUNT(Sampledata_101220224[NumIdentificationHome])  

Place the Measure in the Table Visual

• Add the column to the Rows section of your table visual.NameTerritory
• Add the measure to the Values section.CountHouseholdsByTerritory

This setup will display the distinct count of households for each territory in the respective rows and the total distinct count across all territories in the grand total row.

Please refer to the attached screenshot:

If you find this post helpful, kindly mark it as the solution to assist other members in locating it easily.

Regards,

Pavan.

[wini_R]

Hi @Mikaseido1,

It looks like you need a solution in DAX but posted your question in Power Query forum 🙂

If your table is as simple as in your example above the following formula should work fine:

Count = COUNTROWS(DISTINCT(tbl[numIdentificacionHogar]))

 But if the numIdentificacionHogar column contains same values for different nombreTerritorio, then the formula above might return the unexpected total - it won't be a sum of all rows.  If you want it to be a total of all rows, then please try the following formula:

Count2 = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(tbl, tbl[nombreTerritorio], tbl[numIdentificacionHogar]),
        "@count", CALCULATE(DISTINCTCOUNT(tbl[numIdentificacionHogar]))
    ),
    [@count]
)

[Omid_Motamedise]

Do you want to do it by power query or Dax?
in power query just right click on the first column, select Group By and then in the opend window pick count row as operator

in the dsax just use CountRows fonctuin and use the table name is its input