Calculated column that filters by row level based on a column from another table

[BnicksTrin]

I need to create a calculated column that will return a value based on whether the primary key equlas a foreign key, i.e Opportunity(ID) - Contract(Opportunity ID), AND if a specific expression in another column meets an argument, Contract(RecordTypeName) = "Contract Requested". Any help?

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

RecordTypeName CC =
VAR _opportunitycondition =
    COUNTROWS (
        FILTER ( RELATEDTABLE ( Opportunity ), Opportunity[condition] = "yes" )
    ) <> 0
VAR _reasoncondition =
    COUNTROWS (
        FILTER ( RELATEDTABLE ( Reason ), Reason[description] = "acceptable" )
    ) <> 0
RETURN
    IF ( _opportunitycondition && _reasoncondition, " Contract Requested" )

[BnicksTrin]

The Opportunity table and Contracts are the only two tables Im working with. The relationship is one to many based on the columns Opporunity(ID) and Contracts(Opporunity ID). In the Contracts table, there are columns that specify the type of record associated with an Opportunity ID: Contracts Renwal, Contracts Request, Pre-sales request, etc..I need to create a column in the Opportunity table that lists the record type for the Opportunity ID but identifies only opporutnity IDs that DO NOT have a contract request.