Forum Discussion

AnonJ's avatar
AnonJ
New Member
1 year ago
Solved

Not directly related table using in calculation

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]))

 

 

 

 

 

 

 

 

 

 

 

Need Help
  • _AAndrade's avatar
    _AAndrade
    1 year ago

    Hi AnonJ,

    Please try this: 

     

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

RETURN
  _Result

     

3 Replies

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    1 year ago

    Hi AnonJ,

    Please try this: 

     

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

RETURN
  _Result

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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

  • AnonJ's avatar
    AnonJ
    New Member
    1 year ago

    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