how to filter out Blanks and Infinity

[mohammedkhan]

I want to show the Min and Max Pop Growth, in both cases i am either getting Infinity (because i didnt use the blank function) or getting blanks 

Can i filter out the Inifinities and blanks from the visualization or perhaps from the DAX.

[v-frfei-msft · Accepted solution]

Hi @mohammedkhan ,

1.You can going to the Visual level filters and setting Pop LY/Pop Growth/Pop Growth1 is not blank.  the specific row will be filtered out then.

2.Right click the Blank/infinity cell in the report, select the Exclude. The result will be filtered same as above.

3.Infinity appeared when you using “/”, you can use Divide function instead: DIVIDE(numerator,denominator,0), the infinity value will be replaced by”0” as below (I scratched the data with only 3 rows related just for ease)

4.If you’d like to suppress the blank in column Pop LY, you can add another measure to replace it, (Blank has been changed to “0”), and then use Divide function to generate the Pop Growth. (using “0” straightway as above is also optional)

Measure = IF(SUM(Table1[Pop LY])=BLANK(),0,SUM(Table1[Pop LY]))

Measure 2 = var a = DIVIDE([Diff Pop],[Measure])
return
IF(ISBLANK(a),0,a)

[Anonymous]

@mohammedkhan -

A few possibilities:

1. Make all of the measures return blank - then the row can be filtered out automatically. The Total Population measure would need to check whether there is previous year data and return blank if there is none.

2. Filter your results by some static time range.

3. Filter your results by a dynamic time range - add a Calculated Column to your table that specifies whether the data is the earliest year for that state.

Hope this helps,

Nathan