Forum Discussion
Counting Values from another column
Hey (first post here so I'll try to be thorough) - an example of my table below.
ID TITLE CLI Campaign Call Reason
1 111 <null> New sale <null>
2 <null> 111 <null> Sales Query
3 333 <null> Cancel <null>
4 <null> 333 New Sale Sale sold
5 333 <null> New Sale Sale not sold
6 333 <null> Cancel Sale sold
Background - we send some SMS's to customers and im trying to determine how many of them callback on the back of an SMS and then do some matching on the Campaign/Reason (which isnt an exact match - annoying but not major).
However I'm having real trouble using the measures/adding column/writing DAX transformations to effectively have an additional column created based on whetther the CLI and the TITLE match....
Any ideas?
hey,
think ive managed to fix it from using a very old thread using the below - seems to do the trick
Callback on SMS =VAR _B =FIRSTNONBLANK ( Call_List[TITLE], "" )VAR _C =CALCULATE (COUNTROWS ( 'Call_List' ),FILTER ( ALL ( 'Call_List' ), 'Call_List'[CLI] = _B ))RETURNIF ( ISBLANK ( _C ), 0, _C )
7 Replies
To add to the above (cant edit)
The resulting table would be something likeID TITLE CLI Campaign Call Reason CallbackonSMS
1 111 <null> New sale <null> 0 or no
2 <null> 111 <null> Sales Query 1 or yes
3 333 <null> Cancel <null> 0 or no
4 <null> 333 New Sale Sale sold 1 or yes
5 333 <null> New Sale Sale not sold 0 or no
6 333 <null> Cancel Sale sold 0 or no
Use below formula in a Calculated column
=if('Table'[TITLE]='Table'[CLI],"Yes","No")
Replace Table with your table name.Hey,
Unfortunately this wouldnt work as each row is individual, if it has a number in the TITLE it has a null CLI and vice versa
Then use this
=if('Table1'[TITLE]=0,"Yes","No")
Again, unfortunately not.
May be worth adding that not every customer that rang in (had a CLI) would have an SMS sent to them, likewise not every customer who had an SMS sent to them would have called in (CLI)
Can you document your criteria for Yes or No? Above formula will give result as per data set and result posted by you.
If you can post data where the formula is not giving the result and the reason for that, then right formula can be made.
