Number of days between two dates only if at least one record exists in a related table

[Anonymous]

Hi,

I am new to Power BI and to DAX, and I am trying to create a measure.

For this question, we need to consider 2 fact tables and a few fields for each:

• FactOpportunity
  • Id
  • Name
  • PRC_signoff_complete
  • Flipped_to_Proposal
• FactOpportunityRequest
  
  • Id
  • OpportunityId (foreign key to FactOpportunity[Id])
  • Name

My goal is to calculate the number of days for each row, between PRC_signoff_complete and Flipped_to_Proposal, only when there is at least one record for that Opportunity in the FactOpportunityRequest table.

I was able to get the number of days by using the following syntax:

SWITCH (
        TRUE (),
        FactOpportunity[PRC_signoff_complete] > FactOpportunity[Flipped_to_Proposal], DATEDIFF ( FactOpportunity[Flipped_to_Proposal], FactOpportunity[PRC_signoff_complete], DAY ),
        FactOpportunity[PRC_signoff_complete] < FactOpportunity[Flipped_to_Proposal], DATEDIFF ( FactOpportunity[PRC_signoff_complete], FactOpportunity[Flipped_to_Proposal], DAY ) * -1
    )

Now I am stuck on the filtering, to get the number of days only when there is at least one record for that Opportunity in the FactOpportunityRequest table. If there is no record, it should be blank.

Any idea how to do that?

[tamerj1 · Accepted solution]

Hi @Anonymous 

Please try

=
IF (
    NOT ISEMPTY ( RELATEDTABLE ( FactOpportunityRequest ) ),
    VAR SignDate = FactOpportunity[PRC_signoff_complete]
    VAR FlipDate = FactOpportunity[Flipped_to_Proposal]
    VAR MinDate =
        MIN ( SignDate, FlipDate )
    VAR MaxDate =
        MAX ( SignDate, FlipDate )
    VAR NumOfDays =
        DATEDIFF ( MinDate, MaxDate, DAY )
    RETURN
        IF ( SignDate > FlipDate, NumOfDays, - NumOfDays )
)

[Anonymous]

Thank you @tamerj1 that helps a lot!