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

 

 

 

 

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.

2 Replies

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