How to filter for a text value and then add a new dummy value in place of what's there already?

[Anonymous]

I have this formula below whichworks fine, but I need to add a new type of filter. I would like to be able to say for every instance that a measure called routing is equal to "x" to count that score as 750. 

Approvable Population = 
DIVIDE (
  CALCULATE ( COUNTROWS ( LNApps_Facts ), 'LNApps_Facts'[FICO_SCORE] >=600 
 && 'LNApps_Facts'[FICO_SCORE] <= 850)
,
sum(LNApps_Facts[Application_Count]))

Right now the values within the routing measure are not correct, and we would like to place a dummy value of 750 into the calculation. So I need this formula to also find within the routing measure where is equal to the text "x" and to also place a 750 value in place of whatever is there.

How can I do this? 

[Anonymous]

I have figured out I can try to create a new column for these new dummy values. Like below: 

X Values = IF(VALUES(LNApps_ApplicationDim[Routing])="X", 750)

Instead of substituting values, another option is to just count if the name is "X" in the routing column also in the same formula. The problem is that I can't have more than one column referenced. This is what I was trying to do below. 

Approve Rate >600 = 
DIVIDE (
  CALCULATE ( COUNTROWS ( LNApps_Facts ), 'LNApps_Facts'[FICO_SCORE] >=600
 && 'LNApps_Facts'[FICO_SCORE] <= 850
|| 'LNApps_Applicant'[Routing] = "X"
    ),
 CALCULATE (
        COUNT ( LNApps_Facts[FICO_SCORE] ),
        LNApps_ApplicationDim[Status] <> "Declined"
            || LNApps_ApplicationDim[Status] <> "No Offer Match"
    ),0
) +0

How can I overcome this error: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression

[v-sihou-msft · Accepted solution]

@Anonymous

Just add FILTER() into both CALCULATE() function.

Approve Rate >600 =
DIVIDE (
    CALCULATE (
        COUNTROWS ( LNApps_Facts ),
        FILTER (
            'LNApps_Facts',
            'LNApps_Facts'[FICO_SCORE] >= 600
                && 'LNApps_Facts'[FICO_SCORE] <= 850
                || 'LNApps_Applicant'[Routing] = "X"
        )
    ),
    CALCULATE (
        COUNT ( LNApps_Facts[FICO_SCORE] ),
        FILTER (
            LNApps_ApplicationDim,
            LNApps_ApplicationDim[Status] <> "Declined"
                || LNApps_ApplicationDim[Status] <> "No Offer Match"
        )
    ),
    0
)
    + 0

Regards,

[Anonymous]

@Sean you helped me a lot with this same formula yesterday..