MINX excluding zeros

DAX DAX Calculations
1

OK so I think I am missing something simple here, and it’s probably because I am just back from a two week break and my brain isn’t fully engaged just yet but anyway…

I need to exclude zeros (and negatives) from a MINX calculation and cannot figure out how to do it?

I need to create some final cost projections and want to use a variety of historical performance stats as the basis (e.g. forecast based on current performance score, best score and worst score), but for new projects I will sometimes have a zero performance score and need to exclude these values from what I am trying to achieve as this returns a result of infinity which is useless

Guessing there is a straight forward technique to add into my MINX dax formula that I have missed?

2

Somehow in the table part of the formula, you’ll want to place some logic in like the below to restrict the table to values above zero.

For example

MINX(
    FILTER( Table, ColumnName > 0 ),
        Measure )

Would this work for what you need you think?

3

Thanks Sam

Not quite as I already have a filter applied to my measure as below so I cannot add another within the MINX syntax - is there a way to have multiple filtering in here?!

If I remove the existing KeepFilters then your suggestion does as expected but then I have lost the other part of my filtering

Worst CPI = 
MINX(
	KEEPFILTERS(VALUES('ReportDateSummary'[ReportDate])),
	CALCULATE([CPI]))

I tried adding the filter within my calculate but this doesn’t get the required result either

Dave

4

I’m a bit confused about why using KEEPFILTERS?

Possible to show me table with results and important part of the model?

What about if you actually create a measure with the filter added, then bring in the measure?

I’m just not sure about this KEEPFILTERS requirement though. I need to see it to understand more. I rarely if ever have to use this function to get results I need, so it will be good to check this.