Not directly related table using in calculation

[AnonJ]

I have the below table layout and  I created the datamodel based on the columns highlighted in the yellow. Basically in the contract table in the center contractid is primary key But both  invoice and contractsnapshot table both can have many records for same contractid.

The result I would like to get is the last green table. Everything ok except the disticntcontractcount field.  I am not able to get to the invoice.invyr in the formula its throwing error.  Please help me how to solve this ?

DistinctContractcount= CALCULATE(DISTINCTCOUNT(ContractSnapshot[ContractID]),ContractSnapshot[SnapshotYear]=Related(Invoice[InvYr]))

[_AAndrade · Accepted solution]

Hi @AnonJ,

Please try this: 

VAR _Year = MAX(Invoice[InvYr]
VAR _Result = 
     CALCULATE(
               DISTINCTCOUNT(ContractSnapshot[ContractID])
               FILTER(
                      ALL(ContractSnapshot),
                      ContractSnapshot[SnapshotYear] = _Year
                )
     )

RETURN
  _Result

[AnonJ]

Thank you , sorry its not working, its gives me 3 contracts for both 2021 and 2022 years where it should be 3 for 2021 and 2 for 2022.  Secondly, I am unable to establish a relationship between invoice and contractsnapshot. The relationship to these tables I set up using the middle table contract

[Kedar_Pande]

Try:

DistinctContractCount = 
CALCULATE(
DISTINCTCOUNT(ContractSnapshot[ContractID]),
ContractSnapshot[SnapshotYear] = MAX(Invoice[InvYr]) // Reference the current invoice year context
)

Verify that relationships among the tables are set appropriately, especially between Invoice and ContractSnapshot.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn