Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Sean
Community Champion
Community Champion

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

View solution in original post

Sean
Community Champion
Community Champion

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

View solution in original post

8 REPLIES 8
Sean
Community Champion
Community Champion

@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
Not applicable

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
Community Champion
Community Champion

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

Anonymous
Not applicable

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

@Sean

Sean
Community Champion
Community Champion

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.