Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |