Infinity value, inserting if isblank problem

[Anonymous]

approved of approvable = CALCULATE(COUNTROWS(LNApps_Facts),'LNApps_Facts'[FICO_SCORE]>600)/ CALCULATE(count(LNApps_Facts[FICO_SCORE]), LNApps_ApplicationDim[Status] ="Approved",LNApps_ApplicationDim[Status] ="Funds_Released",LNApps_ApplicationDim[Status] ="Booked")

This is returning an infinity value. Not sure where to insert if(isblank(), "") to combat this. 

[Sean · Accepted solution]

EDITED:

@Anonymous

Change the commas to the OR operator like this...

approved of approvable =
DIVIDE (
    CALCULATE ( COUNTROWS ( LNApps_Facts ), 'LNApps_Facts'[FICO_SCORE] > 600 ),
    CALCULATE (
        COUNT ( LNApps_Facts[FICO_SCORE] ),
        LNApps_ApplicationDim[Status] = "Approved"
            || LNApps_ApplicationDim[Status] = "Funds_Released"
            || LNApps_ApplicationDim[Status] = "Booked"
    ),
    0
)

It should work now

[Sean]

@Anonymous

Try using the DIVIDE function instead of the / operator

approved of approvable =
DIVIDE (
    CALCULATE ( COUNTROWS ( LNApps_Facts ), 'LNApps_Facts'[FICO_SCORE] > 600 ),
    CALCULATE (
        COUNT ( LNApps_Facts[FICO_SCORE] ),
        LNApps_ApplicationDim[Status] = "Approved",
        LNApps_ApplicationDim[Status] = "Funds_Released",
        LNApps_ApplicationDim[Status] = "Booked"
    ),
    0
)

http://sqlblog.com/blogs/marco_russo/archive/2014/07/24/divide-vs-division-operator-in-dax.aspx

http://www.sqlbi.com/articles/divide-performance/

[Anonymous]

Thank you so much. That definitely solves my issue, but now when I use that formula it returns (blank).

I tried calculating the first part of the formula to try to separate out what the issue is and this part below successfully returned a value. 

 CALCULATE ( COUNTROWS ( LNApps_Facts ), 'LNApps_Facts'[FICO_SCORE] > 600 )

With that reasoning I believe the issue is something with trying to filter those statuses. I am very new to Power Bi and am unsure if there may be an issue with trying to filter a value using values from a totally separate table. The two tables do have a connected relationship though. 

[Sean · Accepted solution]

EDITED:

@Anonymous

Change the commas to the OR operator like this...

approved of approvable =
DIVIDE (
    CALCULATE ( COUNTROWS ( LNApps_Facts ), 'LNApps_Facts'[FICO_SCORE] > 600 ),
    CALCULATE (
        COUNT ( LNApps_Facts[FICO_SCORE] ),
        LNApps_ApplicationDim[Status] = "Approved"
            || LNApps_ApplicationDim[Status] = "Funds_Released"
            || LNApps_ApplicationDim[Status] = "Booked"
    ),
    0
)

It should work now

[Anonymous]

Quick question, what's the easiest way to add to that same formula to have scores >=600 but <=850?

@Sean

[Sean · Accepted solution]

@Anonymous

Well this time you have to use the AND operator

approved of approvable =
DIVIDE (
    CALCULATE (
        COUNTROWS ( LNApps_Facts ),
        'LNApps_Facts'[FICO_SCORE] >= 600
            && 'LNApps_Facts'[FICO_SCORE] <= 850
    ),
    CALCULATE (
        COUNT ( LNApps_Facts[FICO_SCORE] ),
        LNApps_ApplicationDim[Status] = "Approved"
            || LNApps_ApplicationDim[Status] = "Funds_Released"
            || LNApps_ApplicationDim[Status] = "Booked"
    ),
    0
)

Good Luck!

[Anonymous]

You're awesome thank you, this community is helping me learn so much. 

[Anonymous]

approved of approvable = CALCULATE(COUNTROWS(LNApps_Facts),'LNApps_Facts'[FICO_SCORE]>600)/ CALCULATE(count(LNApps_Facts[FICO_SCORE]), LNApps_ApplicationDim[Status] ="Approved",LNApps_ApplicationDim[Status] ="Funds_Released",LNApps_ApplicationDim[Status] ="Booked")

This is returning an infinity value. Not sure where to insert if(isblank(), "") to combat this. 

[Sean]

Duplicate Post

http://community.powerbi.com/t5/Desktop/Infinity-value-inserting-if-isblank-problem/m-p/197575#M8693...