Forum Discussion

Quindici's avatar
Quindici
Frequent Visitor
6 years ago
Solved

Relationship using an expression

Hi Everyone,   I am trying to create a calculated column in one table that has no relationship to a second table.   My first table has a date field (ExpectedClose). My Second Table has non-consec...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Quindici ,

     

    We can create a calculated column using following measure to meet your requirement:

     

     

    _LTP =
MAXX ( FILTER ( 'PIPELINE', [EXPECTED_CLOSE_DATE] <= [EXPECTED_CLOSE] ), [LTP]

     

     


    If it doesn't meet your requirement, Please show the exact expected result based on the Tables in my picture.


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Quindici ,


    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

Hello Quindici 

Yes, what you are wanting to do should be possible.  Can you share a sample of your data in a format that can be copied (not a screen shot)?  Make sure it is enough of a sample to represent the scenario (multiple dates, etc)

  • Quindici's avatar
    Quindici
    Frequent Visitor
    6 years ago

    Hi, thanks for joining in with this. After I posted last night I went to bed and this morning tried this...

     

    _LTP =
    VAR mydate =
    CALCULATE (MIN ( PIPELINEGLPERIODMAP[EXPECTED_CLOSE].[Date] ),
    FILTER (
    CALCULATETABLE ( VALUES ( PIPELINEGLPERIODMAP ) ),
    PIPELINEGLPERIODMAP[EXPECTED_CLOSE] >= PIPELINE[EXPECTED_CLOSE_DATE].[Date]
    )
    )
    return
    calculate(SELECTEDVALUE(PIPELINEGLPERIODMAP[LTP]),PIPELINEGLPERIODMAP[EXPECTED_CLOSE]=mydate)
    This seems to work as I needed  it to. I would be thrilled to know if there is a better/faster/less code/simply more elegant/flashier way to do this though. Seems that there are many ways to achieve these tasks in DAX - Which I am quite inexperienced with. I am at heart a SQL guy :-(
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      Share some data and show the expected result.

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi Quindici ,

       

      We can create a calculated column using following measure to meet your requirement:

       

       

      _LTP =
MAXX ( FILTER ( 'PIPELINE', [EXPECTED_CLOSE_DATE] <= [EXPECTED_CLOSE] ), [LTP]

       

       


      If it doesn't meet your requirement, Please show the exact expected result based on the Tables in my picture.


      BTW, pbix as attached.

       

      Best regards,

      Community Support Team _ Dong Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.