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-consecutive dates (Period) and an Identifier (LTP).

 

My new Column in Table 1 would contain the LTP value from the second table for the value of Table2.Period which is equal to or lower than Table1.ExpectedClose.

 

Is such a relationship or expression to return a value even possible? I tried many things, but sadly got nothing that worked. If anyone can help me I would be really grateful.

 

Thanks

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.

5 Replies

  • 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 :-(
  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    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.