Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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,
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
@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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |