topic Re: Number of days between two dates only if at least one record exists in a related table in DAX Commands and Tips

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

Anonymous — Tue, 21 Jun 2022 14:59:28 GMT

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?

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

tamerj1 — Tue, 21 Jun 2022 15:27:22 GMT

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

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

Anonymous — Tue, 21 Jun 2022 15:50:23 GMT

Thank you tamerj1 that helps a lot!