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.

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.