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
Solved! Go to Solution.
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.
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😀
Hi , here is my model
thank you for reponding so quickly. However I am receiving this error
Richard
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])
)
)