Translating where clause or statement in SQL to Power BI

[johnsiah91]

Hi,

I am having a SQL query to get the number of permits falling under the following where statement:

WHERE (P.APPLYDATE BETWEEN @STARTDATE AND @ENDDATE
OR P.ISSUEDATE BETWEEN @STARTDATE AND @ENDDATE)

I loaded the code in Power BI and named this PermitDim. I then created a DateDim, and then link APPLYDATE from PermitDim to CalendarDate in DateDim, and have an inactive relationship that points ISSUEDATE to CalendarDate

I am trying to create a measure in Power BI that will give me the count of permits that falls under the Where statement above. I tried using USERELATIONSHIP but it does not work because I am only pointing one date field at one time. Is there a way to tell DAX to look at both relationships at the same time and then give me a count?

Any help will be greatly appreciated! Thanks!

[johnsiah91]

@CNENFRNL 

Thank you so much for this. I tried building on your DAX code but got stuck again. Please allow me to expand a little further.

To recap:

This is what I get from the where clause

WHERE (P.APPLYDATE BETWEEN @STARTDATE AND @ENDDATE
OR P.ISSUEDATE BETWEEN @STARTDATE AND @ENDDATE)

My goal is to get an average of the sum of the PR_TIME_DAYS based on COMPLETEDATE. 

And this is what I have done so far:

Using the code you shared with me, I am able to get the count of COMPLETEDATE, which is 5.

The question now is how do I get the sum of the PR_TIME_DAYS based on the 5 rows, which is supposed to 20.

Thanks!

[CNENFRNL]