Forum Discussion
NEED HELP // ERROR AL CONTAR
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.
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.
- Add the column to the Rows section of your table visual.
6 Replies
v-pnaroju-msftCommunity Support
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- Omid_Motamedise
Super User
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 
wini_RSolution Supplier
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] )- v-pnaroju-msft
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.
- Add the column to the Rows section of your table visual.
- 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 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
