count rows with 2 tables without relationship

Question

&nbsp;I have 2 tables.TblUnits&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UnitIDBusinessUnitIDBuildingIDUnitStatusIDUnitCodeUnitShortNameUnitTypeIDExploitationDateFromExploitationDateToOwnerContactIDObjectID&nbsp;176122865460720317-0040.04115522-12-2021 00:0028-3-2022 00:001479362543798Not in exploitation176112865459820317-0030.03115522-12-2021 00:00&nbsp;1479352543797Has no contract176712865385720278-5420278-54&nbsp;9834-2-2022 00:00&nbsp;278442581121Has an contract176502865512720344-4320344-4398320-1-2022 00:00&nbsp;858592565732Has an contract&nbsp;and.TblContract&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ContractIDBusinessUnitIDContractNumberCustomerIDContactIDMainUnitIDDateContractSignedDateContractFromDateContractToObjectIDActief176232862526319574148982176718-3-2022 00:008-3-2022 00:00&nbsp;259883611757928625237195481532721765022-2-2022 00:0022-2-2022 00:00&nbsp;258602711732628625118192821508111761122-12-2021 22:231-1-2022 00:0025-3-2022 00:0025438900&nbsp;there is a non-actief relationship between MainUnitID and UNITID.&nbsp;&nbsp;I use for to count active contracts =&nbsp;CALCULATE( COUNTROWS(tblContract),IF(AND(tblContract[DateContractFrom]&lt;=max(dates[date]),OR(tblContract[DateContractTo] &gt;= max(dates[date]), tblContract[DateContractTo]= 0)), 1,0))And for active unit count i use =&nbsp;CALCULATE(COUNTROWS(tblUnit), IF(AND(tblUnit[ExploitationDateFrom]&lt;=max(dates[date]),OR(tblUnit[ExploitationDateTo] &gt;= max(dates[date]), tblUnit[ExploitationDateTo]= 0)), 1,0))&nbsp;But now i need to count the free units of both tables on a max(dates[date]). That is a unit who is active with no contract. In this example is that 1 unit. nr. 17611.And i would like to count the units wiht an contract. In this example is that 2 with numbers 17671 and 17650.&nbsp;&nbsp;Can anyone help me with a solution?&nbsp;Thanks!!&nbsp;&nbsp;&nbsp;

djzijlstra · Accepted Answer

Intersect was not working for me, but i have found the solution.&nbsp;

Forum Discussion

count rows with 2 tables without relationship

2 Replies

Related content

Relationship with Fact tables

Relationship

Relationship Issue

Relationships

Creating relationships

TblUnits
UnitID	BusinessUnitID	BuildingID	UnitStatusID	UnitCode	UnitShortName	UnitTypeID	ExploitationDateFrom	ExploitationDateTo	OwnerContactID	ObjectID
17612	286	5460	7	20317-004	0.04	1155	22-12-2021 00:00	28-3-2022 00:00	147936	2543798	Not in exploitation
17611	286	5459	8	20317-003	0.03	1155	22-12-2021 00:00		147935	2543797	Has no contract
17671	286	5385	7	20278-54	20278-54	983	4-2-2022 00:00		27844	2581121	Has an contract
17650	286	5512	7	20344-43	20344-43	983	20-1-2022 00:00		85859	2565732	Has an contract

TblContract
ContractID	BusinessUnitID	ContractNumber	CustomerID	ContactID	MainUnitID	DateContractSigned	DateContractFrom	DateContractTo	ObjectID	Actief
17623	286	25263	19574	148982	17671	8-3-2022 00:00	8-3-2022 00:00		2598836	1
17579	286	25237	19548	153272	17650	22-2-2022 00:00	22-2-2022 00:00		2586027	1
17326	286	25118	19282	150811	17611	22-12-2021 22:23	1-1-2022 00:00	25-3-2022 00:00	2543890	0