DAX COUNT IF Based on Date from Another Table

[asjones]

I am trying to create a Measure that counts projects based  on a date. If I do the count and compart the dates in the table to a hard coded date in DAX things work  fine. However if  I try and use a date field from antoher table things fail.

I started with the follwing formulas

Count Proj 1 = countrows( FILTER(Main_Proj_Master, Main_Proj_Master[Project Completed Date] >=  date(2017,07,01) ))

Count Proj 2 = CALCULATE( DISTINCTCOUNT( Main_Proj_Master[Main_Project]),   Main_Proj_Master[Project Completed Date] >=  date(2017,07,01)     )

However I really wanted the date field to be dynamci based on the date field from antoher table. I did not want to (as there could be many relations needed), but I created a many to one relation from Main_Proj_Master[Project Completed Date]  to a table called Date_Info[Date_From]. 

I modified the formulas

Count Proj 3 =
    countrows( FILTER(Main_Proj_Master, Main_Proj_Master[Project Completed Date] >= RELATED(Date_Info[Date_From] )  ) )

This versin did not seem to actually filter anything and just returned the complete total as if there was no filter.

Count Proj 4 =
CALCULATE( DISTINCTCOUNT( Main_Proj_Master[Main_Project]),  Main_Proj_Master[Project Completed Date] >= RELATED(...

Oddly enough in this case the RELATED functin does not even seem to understand and find the Date_Info[Date_From] column.

I am missing something and lost at this point.  Thoughts?

thanks for any an all help. I know there may be multiple suggestoins so would love to see optoins and understand pros/cons between.

thanks

Alan

[v-xjiin-msft · Accepted solution]

Hi @asjones,

Since you have created a many to one relationship between Main_Proj_Master[Project Completed Date]  to a table called Date_Info[Date_From]. And you are using RELATED() function in your expression, you should know that RELATED() function returns a single value that is related to the current row. 

Which means that you are always doing something like: Main_Proj_Master[Project Completed Date] >= Main_Proj_Master[Project Completed Date]. That's why it returns the complete total.

Then to achieve your requirement, first the condition should be a single specific date like date(2017,07,01) which depends on your logic. Then I think there's no need to create a relationship between the two table. You can try something like below, compare with the MAX date in table Date_Info:

Count Proj 3 =
COUNTROWS (
    FILTER (
        Main_Proj_Master,
        Main_Proj_Master[Project Completed Date] >= MAX ( Date_Info[Date_From] )
    )
)

Thanks,
Xi Jin.

[asjones]

@v-xjiin-msft,

Thanks for the response. I had the relation as I think someone suggested it in the past. I removed it and used your code and it worked perfect.  As a learning process for me I would like to understand why I can just have

Main_Proj_Master[Project Completed Date] >=  Date_Info[Date_From]

Why do i need MAX() ? The Date_Info table has a single role of fields. Power BI complains it can't find a single value, but there is just one.

Thanks again for the help.

Alan

Count Proj 3 =
COUNTROWS (
    FILTER (
        Main_Proj_Master,
        Main_Proj_Master[Project Completed Date] >= MAX ( Date_Info[Date_From] )
    )
)