topic Re: I can't filter zero in DAX Commands and Tips

I can't filter zero

CarlesWRC — Thu, 14 Sep 2023 06:39:37 GMT

Hi All!

I'm trying to make something pretty easy, but I'm going crazy right now because I really don't understand how to solve this. I made similar stuff like that and is the first time that I get something like this.

I have a table, and I want to count per reference every reference that the sum of money is more than 20.

Example: Each reference where the sum is greater than 20, it has to count one time (despite that maybe for this reference we have the same reference X repeated times).

ENTRADAS KPI 20 = CALCULATE(DISTINCTCOUNT(SE_PBI_PASOS_GRPZ_SEGM_MARCA[NUMBAS]), FILTER(SE_PBI_PASOS_GRPZ_SEGM_MARCA,[PVP TY]>20))

As you can see on the second pic, 0 is counting as 1, but it shouldn't, because I just want to count starting with 20 $.

Thank you very much!!

Greetings.

Re: I can't filter zero

DataNinja777 — Thu, 14 Sep 2023 07:01:00 GMT

Hi CarlesWRC ,

What about a dax formula like below? I've manually recreated your PBI screen print and it seems to produce your required output.

I attach the link to the pbix file below:

ENTRADAS KPI 20.pbix

Re: I can't filter zero

CarlesWRC — Thu, 14 Sep 2023 07:46:15 GMT

Thank you very much! It works perfectly. I just changed the 'SE_PBI_GRPZ_SEGM_MARCA'[PVP TY]' by the metric [PVP TY]. The only thing that it seems to not work is the Total. I would expect that the total should show the sum of each time where the sum by reference is greater than 20. But what I get is the count of all distinct references.

I have this:

For example, if I have 111 different references, but only 3 match the logic of greater than 20, so the total should be 3 and not 111...

Thank you in advance!

I don't have a lot of experience and I'm having trouble with this part of the report.

Greetings.

Re: I can't filter zero

DataNinja777 — Thu, 14 Sep 2023 08:02:40 GMT

Hi CarlesWRC ,

If you'd like the total of your measure to add up correctly, please use sumx in a measure over the table which you want the aggregation to work like below:

I attach the link to the updated pbix file below with the above measure:

ENTRADAS KPI 20.pbix

Re: I can't filter zero

CarlesWRC — Thu, 21 Sep 2023 07:33:12 GMT

Hi!

I tried to use SUMX but I don't get what we would expect:

I really don't understand what is happening. Something I'm doing wrong, because this kind of stuff is pretty easy to do in other BI tools..

Thanks for your help!