Forum Discussion
Wrong total - DISTINCT COUNT
Ok, I’ve understood the issue.
It depends on how DataConsiderada is calculated.
Take titulodeproducto = 97 as an example. For this product, DataConsiderada is 16/01/2025, but Data Emissão is 17/01/2025.
So, when producto 97 is in the filter context, it gets skipped because DataConsiderada is lower than Data Emissão.
However, at the total level, there is no filter on titulodeproducto, so DataConsiderada becomes 30/01/2025, and 32 products meet the criteria.
Hope I made my point, this is how filter context works and how dax calculate totals
So the solution to your issue really depends on what you want to achieve but, as I can see, one easy workaround could be.
Create a new calculated column with the definitive date, like you calculated DataConsiderada.
So the defdate column=
dd = IF(ISBLANK('ADP06-FR-0006 Cadastro do Produto'[Data Alterada]),IF(ISBLANK('ADP06-FR-0006 Cadastro do Produto'[Data Definida]),'ADP06-FR-0006 Cadastro do Produto'[Data Preliminar para emissão],'ADP06-FR-0006 Cadastro do Produto'[Data Definida]),'ADP06-FR-0006 Cadastro do Produto'[Data Alterada])then adjust the measure like this:
EmitidosnoPrazo =
VAR EmitidosPrazo =
CALCULATE(
COUNTROWS('ADP06-FR-0006 Cadastro do Produto'),
NOT(ISBLANK('ADP06-FR-0006 Cadastro do Produto'[Data Emissão])),
'ADP06-FR-0006 Cadastro do Produto'[Data Emissão] <= 'ADP06-FR-0006 Cadastro do Produto'[dd],
'ADP06-FR-0006 Cadastro do Produto'[Descrição Revisão] <> "Preliminar"
)
RETURN
EmitidosPrazo
Super UserLooks like you have blank rows?
Could you provide sample data?
I'm attaching a link to a GoogleDrive with a sample file.
Please note that the measure filters correctly but the total display is wrong.
Filter it by the month 1 and you'll see the question posted above, 27 lines to a 31 total. But all the n° lines x totals are wrong.
https://drive.google.com/file/d/1uElaZg9IkiQGdnz2cS0-jWR5SEaHRiEC/view?usp=sharing
- Gabry
Ok, I’ve understood the issue.
It depends on how DataConsiderada is calculated.
Take titulodeproducto = 97 as an example. For this product, DataConsiderada is 16/01/2025, but Data Emissão is 17/01/2025.
So, when producto 97 is in the filter context, it gets skipped because DataConsiderada is lower than Data Emissão.
However, at the total level, there is no filter on titulodeproducto, so DataConsiderada becomes 30/01/2025, and 32 products meet the criteria.
Hope I made my point, this is how filter context works and how dax calculate totals
So the solution to your issue really depends on what you want to achieve but, as I can see, one easy workaround could be.
Create a new calculated column with the definitive date, like you calculated DataConsiderada.
So the defdate column=
dd = IF(ISBLANK('ADP06-FR-0006 Cadastro do Produto'[Data Alterada]),IF(ISBLANK('ADP06-FR-0006 Cadastro do Produto'[Data Definida]),'ADP06-FR-0006 Cadastro do Produto'[Data Preliminar para emissão],'ADP06-FR-0006 Cadastro do Produto'[Data Definida]),'ADP06-FR-0006 Cadastro do Produto'[Data Alterada])then adjust the measure like this:
EmitidosnoPrazo =
VAR EmitidosPrazo =
CALCULATE(
COUNTROWS('ADP06-FR-0006 Cadastro do Produto'),
NOT(ISBLANK('ADP06-FR-0006 Cadastro do Produto'[Data Emissão])),
'ADP06-FR-0006 Cadastro do Produto'[Data Emissão] <= 'ADP06-FR-0006 Cadastro do Produto'[dd],
'ADP06-FR-0006 Cadastro do Produto'[Descrição Revisão] <> "Preliminar"
)
RETURN
EmitidosPrazoThank you so much.