cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)