topic Re: Why total = 0 ? in DAX Commands and Tips

Why total = 0 ?

sportive92003 — Mon, 16 Sep 2024 18:22:09 GMT

Hello,

I did this dax new measure to count the new clients, but the total appears 0. How to count them?

Nouveaux clients (1an) =

Var N1=CALCULATE(COUNT('SG LUBRIFIANTS - 2019-2023 data corrigées'[no_client]),

FILTER(ALL('SG LUBRIFIANTS - 2019-2023 data corrigées'),[no_client]=SELECTEDVALUE('SG LUBRIFIANTS - 2019-2023 data corrigées'[no_client])&&YEAR([date_de_la_facture])=YEAR(TODAY())))

Var N2=CALCULATE(COUNT('SG LUBRIFIANTS - 2019-2023 data corrigées'[no_client]),

FILTER(ALL('SG LUBRIFIANTS - 2019-2023 data corrigées'),[no_client]=SELECTEDVALUE('SG LUBRIFIANTS - 2019-2023 data corrigées'[no_client])&&YEAR([date_de_la_facture])=YEAR(TODAY())-1))

return

IF(N1>0&&N2=BLANK(),1,0)

-----

I've tried this but =0 too:

Nouveaux clients (1an) - count = calculate (([Nouveaux clients (1an)]) , all('SG LUBRIFIANTS - 2019-2023 data corrigées'))

Thanks!

Re: Why total = 0 ?

tamerj1 — Mon, 16 Sep 2024 19:22:39 GMT

Hi sportive92003

Because SELECTEDVALUE returns BLANK at the total level for the fact that there is no single value available in the filter context of the total.
is [date_de_la_facture] a measure a column? If measure, what's the DAX? And what's your reason for using ALL? Is it to remove an existing filter in the table visual or a slice?

Re: Why total = 0 ?

Anonymous — Tue, 17 Sep 2024 03:42:48 GMT

Thanks for the reply from tamerj1 , please allow me to provide another insight:

Hi, sportive92003

Regarding the issue you raised, my solution is as follows:

In response to your question, I offer two approaches:

1.The first is to use the HASONEFILTER () function as the basis for judgment, and then output the SUMX () function as the aggregate.

Here are the related links, I hope you will be helpful:

Dealing with Measure Totals - Microsoft Fabric Community
Measure Totals, The Final Word - Microsoft Fabric Community

2.The second is to convert the measures into groups of calculations and then modify the aggregation:

You can try the following code as a calculated column:

Then modify the way he aggregates:

Best Regards,

Leroy Lu

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