Forum Discussion
Pull calculated measure from another table
Hello!
I have this 2 tables. One table is from FN_Vendor 3. Below fields are created using a measure:
CertCOIGL_MinDate=CALCULATE(MIN(FN_VendCerts[ValidFromDate]),FILTER(FN_VendCerts,FN_VendCerts[CertificationType]="COI GL"))
CertCOIGL_MaxDate = CALCULATE(
MAX(FN_VendCerts[ValidToDate]),
FILTER(FN_VendCerts,FN_VendCerts[CertificationType]="COI GL")
)
I want to pull these data from FN_Vendor3 to FN_Facts Single but unfortunately, it results to null values.
I already created relationship between 2 tables but still doesn't get the desired results
Hope you could help me on this.
Ok, Can you try using with LOOKUPVALUE function instead of RELATED, as LOOKUPVALUE works in both calculated columns and measures.
CertCOIGL_MaxDate =
CALCULATE(
MAX(FN_VendCerts[ValidToDate]),
FILTER(
FN_VendCerts,
FN_VendCerts[CertificationType] = "COI GL" &&
FN_VendCerts[VendorKnownAsName] =
LOOKUPVALUE(FN_Vendor[VendorKnownAsName], FN_Vendor[VendorAccountNumber], <your_column_with_account_number>)
)
)
3 Replies
Hi juhoneyighot - As per above mentioned, If you're pulling the measure into a different table, ensure the filter context flows correctly.Use RELATED to fetch data into the measure if the relationship exists.
CertCOIGL_MinDate =
CALCULATE(
MIN(FN_VendCerts[ValidFromDate]),
FILTER(
FN_VendCerts,
FN_VendCerts[CertificationType] = "COI GL" &&
RELATED(FN_Vendor3[VendorID]) = FN_Facts_Single[VendorID]
)
)check the above logic and hope it works.
rajendraongole1 I bumped on to this error
Ok, Can you try using with LOOKUPVALUE function instead of RELATED, as LOOKUPVALUE works in both calculated columns and measures.
CertCOIGL_MaxDate =
CALCULATE(
MAX(FN_VendCerts[ValidToDate]),
FILTER(
FN_VendCerts,
FN_VendCerts[CertificationType] = "COI GL" &&
FN_VendCerts[VendorKnownAsName] =
LOOKUPVALUE(FN_Vendor[VendorKnownAsName], FN_Vendor[VendorAccountNumber], <your_column_with_account_number>)
)
)
