Relationship using an expression

[Quindici]

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

[v-lid-msft · Accepted solution]

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 · Accepted solution]

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]

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]

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 · Accepted solution]

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.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.