locked
Compare one column with another using in a calculated field formula RRS feed

  • 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]),"3-31")
                                                            ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"3-31")
                                                           )
                           )

    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]),"3-31")
                                                            ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"3-31")
                                                           )
                           )

    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]),"3-31")
                                                            ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"3-31")
                                                           )
                           )

    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! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by Ashish Mathur Saturday, February 23, 2013 7:40 AM
    Friday, February 22, 2013 6:20 PM
    Answerer

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]),"3-31")
                                                            ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"3-31")
                                                           )
                           )

    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! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by Ashish Mathur Saturday, February 23, 2013 7:40 AM
    Friday, February 22, 2013 6:20 PM
    Answerer
  • 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]),"3-31")
                                                            ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"3-31")
                                                           )
                           )

    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