SUMX with Filter which count twice when it wants..

[PatrickByGecko]

Hello

I get a problem with this sum and filter function.

The good result of my sumx is 11,30 for the condition I ask for. I get this number all the time (which is good) for a same client (CIFD here) as you an see underneath . But when the client changes, (MGEN for instance), the number is twice and wrong. I do not understand because my condition does not include any Client Column, It should have no impact on the result. It looks like Sumx function calculate twice at each changement of client. Do not understand.

SUMX(FILTER('Table'; 'Table'[ANNEE] = EARLIER('Table'[ANNEE]) && 'Table'[NUM MOIS]=EARLIER('Table'[NUM MOIS]) && 'Table'[COLLABORATEURId]=EARLIER('Table'[COLLABORATEURId]);'Table'[NB JR HSITE])

[kentyler]

I tried to format your DAX and got and error report 

SUMX(
    FILTER(
        'Table'; 'Table'[ANNEE] = EARLIER('Table'[ANNEE])
        && 'Table'[NUM MOIS]=EARLIER('Table'[NUM MOIS])
        && 'Table'[COLLABORATEURId]=EARLIER('Table'[COLLABORATEURId]);
-----> Missing ) ?
'Table'[NB JR HSITE])
When I do the formatting myself it looks like the closing parens for the FILTER function is missing

[PatrickByGecko]

A ")" has to be add to my condition because I did an extract from my code. But It is not the problem =>

SUMX(FILTER('Table'; 'Table'[ANNEE] = EARLIER('Table'[ANNEE]) && 'Table'[NUM MOIS]=EARLIER('Table'[NUM MOIS]) && 'Table'[COLLABORATEURId]=EARLIER('Table'[COLLABORATEURId]);'Table'[NB JR HSITE]))

here is a bigger screen firts =>

[Anonymous]

Sorry for asking.... but what's the reason for the EARLIER function in your measure? Would you mind explaining it?

As much as I know, EARLIER should be used only when there are at least 2 row contexts present at the same time. In your formula I can't see there being 2 different row contexts...

Thanks.

Best
D

[PatrickByGecko]

I don't understand what you are saying about two "differents row contexts".

SUMX(FILTER('Table'; 'Table'[ANNEE] = EARLIER('Table'[ANNEE]) && 'Table'[NUM MOIS]=EARLIER('Table'[NUM MOIS]) && 'Table'[COLLABORATEURId]=EARLIER('Table'[COLLABORATEURId]);'Table'[NB JR HSITE]))

I'm just trying to sum some days (NB JR HSITE) in relation with different criterias. The output is OK for the fist rows of the table and become wrong (twice wrong) for one row of the table.

Just a remark, I'tried to change with a new formula CALCULAT/SUM/ALL/ FILTER and the problem is the same.

[Anonymous]

Does you model contain only one big fact table?

Best
D

[PatrickByGecko]

My formula adress only one Table "Activites mensuelles" as shown underneath.

Note that I don't understand your word "fact".

[Anonymous]

OK. To make correct calculations in all circumstances, you have to know what a good and correct model is.

To this end, please watch the following first:

https://www.youtube.com/watch?v=78d6mwR8GtA

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Afterwards, please create correct dimensions and fact tables (one big table is not a model, it's a sure way to fail). Connect them in the right way, hide all columns in the fact table(s), all IDs in dimensions and helper columns and then and only then can you start writing simple and correct measures against a good, robust model.

If you ignore this advice, be prepared for some nasty bugs that you will not be able to troubleshoot, not to mention understand or even spot.

You've been warned.

Best
D

[PatrickByGecko]

The problem comes from a lack of index indeed, Thank  you. When I add my new index at beginning of the key fields of my Matrix report the filter works fine. There is no value computed twice. But when I do that my Matrix report is no way because I want to be able to navigate from year, to month number, to customer, to project and to employee from the left panel of the report as you can see underneath. I could manage a new index with all those fields but I will not be able to navigate. What can I do..?

[PatrickByGecko]

Hello

Please, Can somebody ask to my previous message?

Thanks.

[PatrickByGecko · Accepted solution]

I found out the problem.

The syntax of my formula was ok, the table organization (with Index etc..) was ok too.

The problem came from a real line recorded twice that could'nt be seen in the report.

Sorry for all that!

Patrick.

[TomMartens]

Hey @PatrickByGecko ,

I'm not able to read through the screenshot you provided, maybe you can provide a pbix file that contains sample data, upload the file to onedrive or dropbox and share the link.

Nevertheless, you have to be aware that the issue you describe can occur when the table, that is returned by the FILTER statement, contains more than one row, check your filter statement.

Regards,

Tom