Forum Discussion
DAX count if dates < or >= relating date
Hi
I have two tables that are related via the Property Ref column.
Table 1 - Property (1 row per property)
| Property Ref | Address | Inspection Date | DAX - repair count < repair date | DAX - repair count >= repair date |
| 18 | 100 Dax Road | 05/07/2021 | 4 | 1 |
| 35 | 45 Query Street | 12/09/2021 | 1 | 6 |
| 57 | 205 Folding Avenue | 05/07/2022 | 5 | 2 |
Table 1 - Repairs (Possibly many repairs per property)
| Job Ref | Property Ref | Trade | Repair Date |
| 13879 | 18 | ELEC | 11/03/2021 |
| 17312 | 18 | PLUM | 13/03/2021 |
| 16407 | 18 | ELEC | 20/03/2021 |
| 19216 | 18 | CARP | 04/07/2021 |
| 16085 | 18 | PLUM | 18/08/2021 |
| 16232 | 35 | CARP | 08/09/2021 |
| 17039 | 35 | ELEC | 12/09/2021 |
| 18691 | 35 | PLUM | 10/11/2021 |
| 18023 | 35 | ELEC | 13/11/2021 |
| 12530 | 35 | PLUM | 08/12/2021 |
| 13056 | 35 | ELEC | 02/02/2022 |
| 19993 | 35 | ELEC | 22/03/2022 |
| 17772 | 57 | ELEC | 24/03/2022 |
| 16143 | 57 | ELEC | 30/03/2022 |
| 15045 | 57 | CARP | 19/05/2022 |
| 14352 | 57 | ELEC | 28/05/2022 |
| 10209 | 57 | CARP | 04/07/2022 |
| 12114 | 57 | CARP | 18/07/2022 |
| 14367 | 57 | CARP | 25/09/2022 |
I require two DAX functions to count the number of repairs for each property ref
DAX FUNCTION 1 - if the Repair date (repairs table) is less than (<) the Inspection date (property table)
DAX FUNCTION 2 if the Repair date (repairs table) is freaterthan or equal (>=) to the Inspection date (property table)
Here is my expected results
Property table
| Property Ref | Address | Inspection Date | DAX - repair count < repair date | DAX - repair count >= repair date |
| 18 | 100 Dax Road | 05/07/2021 | 4 | 1 |
| 35 | 45 Query Street | 12/09/2021 | 1 | 6 |
| 57 | 205 Folding Avenue | 05/07/2022 | 5 | 2 |
thank you
RIchard
Hi, cottrera
You can try the following methods.
Column:
repair count < repair date = CALCULATE ( COUNT ( Repairs[Property Ref] ), FILTER ( Repairs, [Repair Date] < EARLIER ( 'Property'[Inspection Date] ) && [Property Ref] = EARLIER ( 'Property'[Property Ref] ) ) )repair count > repair date = CALCULATE ( COUNT ( Repairs[Property Ref] ), FILTER ( Repairs, [Repair Date] >= EARLIER ( 'Property'[Inspection Date] ) && [Property Ref] = EARLIER ( 'Property'[Property Ref] ) ) )Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi, cottrera
You can try the following methods.
Column:
repair count < repair date = CALCULATE ( COUNT ( Repairs[Property Ref] ), FILTER ( Repairs, [Repair Date] < EARLIER ( 'Property'[Inspection Date] ) && [Property Ref] = EARLIER ( 'Property'[Property Ref] ) ) )repair count > repair date = CALCULATE ( COUNT ( Repairs[Property Ref] ), FILTER ( Repairs, [Repair Date] >= EARLIER ( 'Property'[Inspection Date] ) && [Property Ref] = EARLIER ( 'Property'[Property Ref] ) ) )Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you😀
Here's a couple of measures with a similar pattern. The RELATED function allows you to access columns on the 1 side of a relationship while iterating the table on the many side.
DAX - repair count < repair date = COUNTROWS( FILTER(Repairs, Repairs[Repair Date] < RELATED('Property'[Inspection Date]) ) )[DAX - repair count >= repair date] = COUNTROWS( FILTER(Repairs, Repairs[Repair Date] >= RELATED('Property'[Inspection Date]) ) )thank you for reponding so quickly. However I am receiving this error
Richard
cottrera what does your model look like?
Hi , here is my model
