Answered by:
Compare one column with another using in a calculated field formula
Question

Hi,
This calculated field formula computes the Distinct session conducted in every fiscal year
=CALCULATE([Distinct sessions] ,DATESBETWEEN(Calendar1[DateKey] ,STARTOFYEAR(LASTDATE(Calendar1[DateKey]),"331") ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"331") ) )
I now want to compute the Distinct session conducted in every fiscal year where one column is equal to another column
=CALCULATE([Distinct sessions],Feedback[Attendees from]=Feedback[Organised by] ,DATESBETWEEN(Calendar1[DateKey] ,STARTOFYEAR(LASTDATE(Calendar1[DateKey]),"331") ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"331") ) )
When I clicked on Check formula, I got this error
Calculation error in measure 'Feedback'[Fiscal Year Distinct sessions conducted independently]: 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.
Please help me correct this formula to get the desired result.
Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com
Friday, February 22, 2013 2:44 PM
Answers

Just wondering if this works
=CALCULATE([Distinct sessions],filter(Feedback, Feedback[Attendees from]=Feedback[Organised by]) ,DATESBETWEEN(Calendar1[DateKey] ,STARTOFYEAR(LASTDATE(Calendar1[DateKey]),"331") ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"331") ) )
If not, can you pass the file with some sample data? (Upload it in skydrive and pass the link?)
Cheers,
Jason  www.SqlJason.com
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :) Marked as answer by Ashish Mathur Saturday, February 23, 2013 7:40 AM
Friday, February 22, 2013 6:20 PMAnswerer
All replies

Just wondering if this works
=CALCULATE([Distinct sessions],filter(Feedback, Feedback[Attendees from]=Feedback[Organised by]) ,DATESBETWEEN(Calendar1[DateKey] ,STARTOFYEAR(LASTDATE(Calendar1[DateKey]),"331") ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"331") ) )
If not, can you pass the file with some sample data? (Upload it in skydrive and pass the link?)
Cheers,
Jason  www.SqlJason.com
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :) Marked as answer by Ashish Mathur Saturday, February 23, 2013 7:40 AM
Friday, February 22, 2013 6:20 PMAnswerer 
Hi,
Thank you for your help. Your formula did not yield any error but the result was still wrong. I got the correct answer via these two formulas
=CALCULATE([Distinct independent sessions] ,DATESBETWEEN(Calendar1[DateKey] ,STARTOFYEAR(LASTDATE(Calendar1[DateKey]),"331") ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"331") ) )
I computed Distinct independent sessions via this formula
=CALCULATE(DISTINCTCOUNT(Feedback[Date]),filter(Feedback,[Attendees from]=[Organised by]))
Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com
Saturday, February 23, 2013 7:40 AM