cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lizsufrinko Member
Member

Infinity value, inserting if isblank problem

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. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Sean Super Contributor
Super Contributor

Re: Infinity value, inserting if isblank problem

EDITED:

@lizsufrinko

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 Smiley Happy

View solution in original post

Sean Super Contributor
Super Contributor

Re: Infinity value, inserting if isblank problem

@lizsufrinko

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! Smiley Happy

View solution in original post

8 REPLIES 8
lizsufrinko Member
Member

Infinity value, inserting if(isblank() problem

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 Super Contributor
Super Contributor

Re: Infinity value, inserting if isblank problem

@lizsufrinko

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/

Sean Super Contributor
Super Contributor

Re: Infinity value, inserting if(isblank() problem

lizsufrinko Member
Member

Re: Infinity value, inserting if isblank problem

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 Super Contributor
Super Contributor

Re: Infinity value, inserting if isblank problem

EDITED:

@lizsufrinko

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 Smiley Happy

View solution in original post

lizsufrinko Member
Member

Re: Infinity value, inserting if isblank problem

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

lizsufrinko Member
Member

Re: Infinity value, inserting if isblank problem

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

@Sean

Sean Super Contributor
Super Contributor

Re: Infinity value, inserting if isblank problem

@lizsufrinko

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! Smiley Happy

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,730)