topic Re: Countries or countx returning blank total but row by row ok. in DAX Commands and Tips

Countries or countx returning blank total but row by row ok.

jlayala — Fri, 01 Jul 2022 21:54:21 GMT

Hi everyone, hope can someone help me.

I have a table that one measure ("usuario nuevo registrados") do not gives me the total. All measures use the same table.

the card show blank as total is in the table.

Usuario Nuevo Registrados = VAR fecha = SELECTEDVALUE ( 'paynom usuarios'[created_at] ) VAR Fecha_min = EOMONTH ( fecha, -1 ) VAR Fecha_max = EOMONTH ( fecha, 0 ) VAR tabla_filtro = FILTER ( ADDCOLUMNS ( 'paynom usuarios', "@Fecha_min", Fecha_min, "@Fecha_max", Fecha_max ), fecha > Fecha_min && fecha <= Fecha_max && 'paynom usuarios'[rol_id] = 3 && 'paynom usuarios'[registrado] = TRUE() ) VAR CONTEO_USUARIOS_NUEVOS = COUNTX ( tabla_filtro, 'paynom usuarios'[username] ) RETURN COUNTROWS( tabla_filtro)

the other measure is more simple

Usuario Registrado = calculate ( COUNTROWS( 'paynom usuarios') , 'paynom usuarios'[registrado] = TRUE() , 'paynom roles'[name] = "employee" )

Re: Countries or countx returning blank total but row by row ok.

tamerj1 — Sat, 02 Jul 2022 07:00:41 GMT

Hi jlayala
Please try

Usuario Nuevo Registrados = SUMX ( TableName, CALCULATE ( VAR fecha = SELECTEDVALUE ( 'paynom usuarios'[created_at] ) VAR Fecha_min = EOMONTH ( fecha, -1 ) VAR Fecha_max = EOMONTH ( fecha, 0 ) VAR tabla_filtro = FILTER ( ADDCOLUMNS ( 'paynom usuarios', "@Fecha_min", Fecha_min, "@Fecha_max", Fecha_max ), fecha > Fecha_min && fecha <= Fecha_max && 'paynom usuarios'[rol_id] = 3 && 'paynom usuarios'[registrado] = TRUE () ) VAR CONTEO_USUARIOS_NUEVOS = COUNTX ( tabla_filtro, 'paynom usuarios'[username] ) RETURN COUNTROWS ( tabla_filtro ) ) )

Re: Countries or countx returning blank total but row by row ok.

jlayala — Tue, 05 Jul 2022 14:27:55 GMT

It works! Thanks. What would be the reason ? is it the aggregate function?

Re: Countries or countx returning blank total but row by row ok.

tamerj1 — Tue, 05 Jul 2022 14:35:12 GMT

Hi jlayala

the reason is SELECTEDVALUE which returns blank in case of multiple values found. Therefore, totals will allways be blank. SUMX restores the lost value by summing the visible values snd aggregate them in the total cell