Sumx not return good total

[Juju123]

Hi, 

I have a problem with SUMX fonction. I have a table in which i want to sum calculated column. 

I create a DAX formula for calculated column : 

TEST = IF([Measure]<>0,1,0)

Then i want to have the sum of this calculated column but it's not work. Power BI return blank value in sum and i don't understand :

Sum Test = SUMX(CAGC_MFG_ECART_PDC_PPPI_REP,[TEST])

Can you explain me why i have not "Total" for column "Test" and why column "Sum test" return blank value while there are value in column "Test" ? 

Thanks

[Ashish_Mathur]

I think you are confusing between measures and calculated columns.  Share some data, explain the question and show the expected result.

[Juju123]

Hi @Ashish_Mathur ,

In fact I want to display the reference number according to the selection made by the user.

User has several filters.
Depending on the filters that it will select, I must display the reference number which is displayed in a card.

Currently I have this table but the final result I have in the table does not match and the result I have in my map does not match also.

For example, to test that my measurement works, I selected the year 2023 in my data.
My table shows me 94 lines but my measure calculate 195 references. I think my measure

I create this Dax formula (it's measure column) : 

Nb Ref. = DISTINCTCOUNT(TABLE[Ref.])

This Dax column depend on this calculated column

Ref. = REF&"-"LIGNES_NUMBER

Sorry but i can't attach pbix file in my comment. When i want to share pbix, i have this error The file type (.pbix) is not supported.

[Ashish_Mathur]

Upload the file to One Drive and share the download link of the PBI file.

[Juju123]

i don't know if you know smash. I put the pbix in this plateform

you can dowload it with this link : 

https://fromsmash.com/Distinct-value-selected-pbi 

[Ashish_Mathur]

I am not comfortable with using that platform.

[Juju123]

@Ashish_Mathur ,

I create download link with One Drive : 

test_distinct_value.pbix

Can you tell me it's ok for you ?

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Juju123]

Thanks @Ashish_Mathur 

i see the pbi and your test and I will back

On the other hand I would like to know why you have 2 for the month of June 2023 and not 1?

[Ashish_Mathur]

Test the result and study the solution thoroughly.  If there are problems, then clearly show the problem and also the expected result.

[Juju123]

I see pbix and the result it's not good. 

The result i want it's : 

The problem is that the measurement counts the number of dates that the reference to per month.

For example for the month of October we have the number 5 because the reference to 5 different dates in October.
Whereas what I would like is if the reference appears 5 times in the month of October then I count the reference only 1 time

[Ashish_Mathur]

PBI file attached

[Juju123 · Accepted solution]

Thanks you so much ! it's works

[Ashish_Mathur]

You are welcome.

[Juju123]

@Ashish_Mathur,

J'ai appliqué la formule dax sur un plus gros échantillon de données et le rapport est très très lent.

Est-il possible de remplacer la fonction résumé par une autre ou d'utiliser une façon de faire en DAX ?

[Juju123]

I find a solution

I create a concatenate column in database and after i use only this dax formula

NEW_NUMBER_REF = COUNTROW('TABLE'[KEY_CARD])

[Kishore_KVN]

Hello @Juju123 ,

Please check the data type of your measure and if it is general or text change it to whole number that may help you in acheiving your requirement.

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[Juju123]

Hi @Kishore_KVN, 

My column it's measure and i have a good forma :