Forum Discussion

asjones's avatar
asjones
Icon for Helper V rankHelper V
8 years ago
Solved

DAX COUNT IF Based on Date from Another Table

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 ...
Need Help
Tutorial Requests
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    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.

v-xjiin-msft's avatar
v-xjiin-msft
Icon for Solution Sage rankSolution Sage
8 years ago

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's avatar
asjones
Icon for Helper V rankHelper V
8 years ago

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