cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Framboosje Regular Visitor
Regular Visitor

from measure to colum

hi i have a calculated measure but want a column instead because of a filter that i need

see below my calculated column.

 

First Project =
VAR mydate =
CALCULATE (
MIN ( Projecten[start_date]);
FILTER (
CALCULATETABLE ( VALUES ( Projecten) );
Projecten[start_date] >= Projecten[Datum in dienst]
)
)RETURN
CALCULATE (
MIN ( Projecten[end_customer]);
FILTER (
CALCULATETABLE ( VALUES ( Projecten ));
Projecten[start_date]= mydate
)
)

 

the story behind the calculated column

Table 1
Name Date in ServiceFirst ProjectSecond Project
Alex K1-1-2018I want here the first project  of Table 2 of Alex K based on project start date vs Date In serviceHere  the second
Jay kay1-2-2018  
Bernd leo1-3-2018  
    
Table 2
Name name ProjectProject Start DateProject End Date
Alex KAA technology2-1-201814-2-2018
Alex KAB technology15-2-201811-3-2018
Alex KAC technology12-3-201814-2-2018
Jay kayAA technology15-2-201819-2-2018
Jay kayAB technology20-2-201811-3-2018
Jay kayAC technology12-3-201814-4-2018
Jay kayAD technology15-4-20181-3-2018
Bernd leoAA technology2-3-201811-3-2018
Bernd leoAB technology12-3-201814-3-2018
Bernd leoAC technology15-3-2018 

 

i have 2 tables and in table one i want to  have the first and scond project of table 2 based on the nearest Project start date in comparison with the date in service

example: Alex K first project is AA technology because 2-1-2018 is the first nearest date tot the date in service 1-1-2018

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: from measure to colum

Hi @Framboosje

Based on my test, this formula can be applied in a calculated column and can get the expected result as you want, you can also modify a little to use  it in a measure. So, I don't understand what are you seeking for?

Measure = 
VAR mydate =
CALCULATE (
MIN ( Sheet1[Project Start Date]),
FILTER (
CALCULATETABLE ( VALUES ( Sheet1) ),
Sheet1[Project Start Date]>= RELATED(Sheet2[Date in Service])
)
)RETURN
CALCULATE (
MIN ( Sheet1[name Project]),
FILTER (
CALCULATETABLE ( VALUES ( Sheet1 )),
Sheet1[Project Start Date]= mydate
)
)

3.png

 

Best Regards

Maggie

1 REPLY 1
Highlighted
Community Support Team
Community Support Team

Re: from measure to colum

Hi @Framboosje

Based on my test, this formula can be applied in a calculated column and can get the expected result as you want, you can also modify a little to use  it in a measure. So, I don't understand what are you seeking for?

Measure = 
VAR mydate =
CALCULATE (
MIN ( Sheet1[Project Start Date]),
FILTER (
CALCULATETABLE ( VALUES ( Sheet1) ),
Sheet1[Project Start Date]>= RELATED(Sheet2[Date in Service])
)
)RETURN
CALCULATE (
MIN ( Sheet1[name Project]),
FILTER (
CALCULATETABLE ( VALUES ( Sheet1 )),
Sheet1[Project Start Date]= mydate
)
)

3.png

 

Best Regards

Maggie