Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
4 years ago
Solved

DAX Help required

Hi

 

I have a model with two tables.  My first Table = tenant table which has a Tenant number, property number and a tenant start date.

The example below shows two different tenants living in one property at different times 

 

Tenant#Property#Tenancy start dateTenancy end  date
12377701/01/202002/03/2021
45677703/03/202101/08/2025

 

My second table contains all the repairs that have taken place in the propery.  I would like a DAX function that  determines if the repair logged date was within the teancy start and end date , in order to populate the new column on the repairs table  highlighted in red with the tenant number.  

Repairs#Property#Repair logged dateDAX = Tenant#
3001077728/03/2020123
3086177717/05/2020123
2908777718/05/2020123
5067377722/06/2020123
7836777728/07/2020123
4433177704/02/2021123
2054677711/02/2021123
9615277713/02/2021123
5145277721/02/2021123
6851977722/04/2021456
8378177708/01/2022456
1238577729/01/2022456
4323177726/08/2022456

 

thank you 

 

Richard

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    cottrera Maybe:

    Tenant # Column =
  VAR __Property = [Property#
RETURN
  MAXX(FILTER('Tenant Table', [Property#] = __Property && [Repair logged date] >= [Tenancy start date] && [Repair logged date] <= [Tenancy end date]),[Tenant])

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    cottrera Maybe:

    Tenant # Column =
  VAR __Property = [Property#
RETURN
  MAXX(FILTER('Tenant Table', [Property#] = __Property && [Repair logged date] >= [Tenancy start date] && [Repair logged date] <= [Tenancy end date]),[Tenant])
  • cottrera's avatar
    cottrera
    Post Prodigy
    4 years ago

    Hi Greg

     

    thank you for your quick response the DAX function works fine 😀