topic Re: Why is my MIN function ignoring 0? in DAX Commands and Tips

Why is my MIN function ignoring 0?

Georgiaoc — Tue, 05 Mar 2024 10:39:48 GMT

Hi there!

I've found a few posts asking how to exclude zero from MIN caluclations but I'm having the opposite problem!

I've used the following expression: MIN = CALCULATE(MIN('TABLE'[VARIABLE])+0)

I have added the '+0' at the end of the expression as I've read in other posts this forces it to inlcude zeros in the output and this has worked for other expressions. I have tried with with and without 'CALCULATE' at the beginning - no difference to the output.

I'm then viewing the output in a table, which includes separate calculations for MAX and MEAN - all working fine. Except MIN shows a min value of 1 for certain groups, which is not true because the raw data clearly shows some individuals within the group have a score of 0 for my variable of interest.

I cannot provide sample data because it's confidential.

Thank you in advance for your help!

Georgia

Re: Why is my MIN function ignoring 0?

JamesFR06 — Tue, 05 Mar 2024 14:52:22 GMT

I think these topics can help you

https://community.fabric.microsoft.com/t5/Desktop/DAX-treats-0-as-BLANK-how-to-prevent-it/m-p/342272

Re: Why is my MIN function ignoring 0?

Georgiaoc — Tue, 05 Mar 2024 15:27:18 GMT

Hi there! Thanks for your reply! Unfortuntately, I had already reviewed those resources and didn't find them helpful. If you have other suggestions, I'd really appreciate it! Thanks 😄

Re: Why is my MIN function ignoring 0?

JamesFR06 — Tue, 05 Mar 2024 15:34:31 GMT

I would have try this :

MIN = CALCULATE(MIN('TABLE'[VARIABLE]))

return

if(isblank(min),0,Min)

Re: Why is my MIN function ignoring 0?

Georgiaoc — Tue, 05 Mar 2024 18:55:18 GMT

Thank you!

So turns out my original expression techincally works - a variation of the one you provided above also works too.

HOWEVER! It seems the problem is actually something within Power BI table behaviour - if I look at raw scores within a table, these do show that some individuals have a MIN score of 0, however, when they're summarized into a group within a table, it shows 1 as the MIN value. Do you understand the logic for this?

Screenshot below shows an example of a group with 4 people in it - with row level detail on the right that shows that some of these people do indeed have a MIN of 0.

Re: Why is my MIN function ignoring 0?

JamesFR06 — Tue, 05 Mar 2024 21:37:12 GMT

Difficult without tables in front.

DPV_MIN is it a rounded result ?