Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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. 

Need Help
  • Sean's avatar
    Sean
    9 years ago

    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 :smileyhappy:

  • Sean's avatar
    Sean
    9 years ago

    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! :smileyhappy:

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago
    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's avatar
    Sean
    Community Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Sean
        Community Champion
        9 years ago

        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 :smileyhappy: