Forum Discussion

johnsiah91's avatar
johnsiah91
Frequent Visitor
4 years ago

Translating where clause or statement in SQL to Power BI

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!

Need Help

2 Replies

  • johnsiah91's avatar
    johnsiah91
    Frequent Visitor
    4 years ago

    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!