Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Taking the min with condition

table 1 have email sent date: table 2 has when they visited the website and they could have visited multiple times. The two tables have ID relationship.   I would like to pull the fir...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    Write this calculated column formula in Table1.  You will need to change the Table and column names

    =CALCULATE(MIN(Table2[Date]),FILTER(Table2,Table2[ID]=EARLIER(Table1[ID])&&Table2[Date]>EARLIER(Table1[Date])))

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

I cannot figure out the mistake there.  Share the link from where i can download the PBI file with my formula already written there.  I will try to correct for the error.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

    Edit the "Cross filter direction" in both relationships to Single.  My calulcated column formula will give the correct result.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      could you send me the working PBIX?  I still have an error stating "EARLIER/EARLIEST refers to an earlier row context which doesn't exist".  I'm not sure that Filter() can call multiple tables.  

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

        You get that error because you are writing that as a measure.  As clearly stated in my previous message, please write your formula as a calculated column formula.  Change the Direction of both relationships from Both to Single.  Here is a screenshot of the result.