Forum Discussion
Dax Scoring System
Hi everyone,
I want to calculate a score based on whether data is present or absent in my dataset.
A.
Personal Email Address = 50 points
If Personal Email is N/A = 10 points
If Personal Email is Blank = 0 points
B.
Business Email Address = 50 points
If Business Email is N/A = 10 points
If Business Email is Blank = 0 points
C.
Preferred Email Address = 50 points
If Preferred Email is "No" = 0 points
Therefore the total maximum score would be 50+50+50 = 150
Every time I've written an IF(ISBLANK expression, blank values have still been calculated as 50 points.
It should be, for instance, A=50 + B=0 + C=50 = 100
I've ensured that the data is trimmed so blank values are ensured.
Please can somebody suggest a formula which would accurately calculate a total within a single field?
I tried this and numerous variants to no avail:
Contact Score =
IF(ISBLANK('YourTable'[Personal Email]) || 'YourTable'[Personal Email] = "N/A", 10, IF(ISBLANK('YourTable'[Personal Email]), 0, 50))
+ IF(ISBLANK('YourTable'[Business Email]) || 'YourTable'[Business Email] = "N/A", 10, IF(ISBLANK('YourTable'[Business Email]), 0, 50))
+ IF(ISBLANK('YourTable'[Preferred Email]), 0, 50)
Thanks in advance!
9 Replies
Can you provide some sample data please and show the desired result?
Hi,
Thanks for your reply!
Based on the scoring system outlined above, the total score would adjust based on the data available:Business Email Personal Email Preferred Email Contact Score "[email protected]" "[email protected]" Business 150 "[email protected]" N/A Business 110 "[email protected]" Business 100 "[email protected]" 50 0 With the formula I outlined above, when the:
Business email contains a value
Personal email is blank
Preferred email is blank
I should be getting a score of 50
But I'm currently getting 100
Some part of the formula must be incorrect?
Or if the formula works correctly, I need to somehow edit my underlying data!Hi MarkButterfield
can you simply write below calulation
Score =
var E= If(ISBLANK(Personal Email),0,If([Personal Email]="N/A",10),50)
var B= If(ISBLANK(Business Email),0,If([Business Email]="N/A",10),50)
var P= If([Preferred Email]="NO", 0,50)
Return E+B+P
If my post helps please give kudos and accept it as solution!
ThanksIf you want to handle ISBLANK() with prefered email then use below calculation
var E= If(ISBLANK(Personal Email),0,If([Personal Email]="N/A",10),50)
var B= If(ISBLANK(Business Email),0,If([Business Email]="N/A",10),50)
var P= If(ISBLANK[Preferred Email] || [Preferred Email]="No",0,50)
Return E+B+PIf my post helps please give kudos and accept it as a solution!
ThanksHi,
Thanks for this. I used this formula, but had to add closed parentheses on Var E And B for the formula to be accepted.
However, even after this formula, I still get an incorrect value.
I created 3 new columns to check whether the values were being read correctly, and that raised no issues.
I really thought that this would be a simple calculation!
I'm still getting a score of 100 when the personal and preferred email columns are blank and "No" respectivelyI have taken your sample data set.
I got the desired result. please create the following Measure :Total Score =var B=IF(MIN('Email Score'[Business Email])="",0,IF(MIN('Email Score'[Business Email])="N/A",10,50))Var P= IF(MIN('Email Score'[Personal Email])="",0,IF(MIN('Email Score'[Personal Email])="N/A",10,50))var Q= IF(MIN('Email Score'[Preferred Email])="" || MIN('Email Score'[Preferred Email])="N/A",0,50)Return (B+P+Q)If I answered your question please give kudos and accept it as a solution!
Thanks
ISBLANK() returns false with an empty string. You probably want to rewrite with a comparison to "".
