Forum Discussion
count rows with 2 tables
Hi there,
I have 2 tables:
| 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 | BLOEMHOF 54 | 983 | 4-2-2022 00:00 | 27844 | 2581121 | Has an contract | |
| 17650 | 286 | 5512 | 7 | 20344-43 | GROEN VAN PRINSTERERSTRAAT 43 | 983 | 20-1-2022 00:00 | 85859 | 2565732 | Has an contract |
and
| 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 |
there is an non-active relationship between MainUnitID and UnitID.
I use for the count of active contracts: CALCULATE( COUNTROWS(tblContract),
IF(AND(tblContract[DateContractFrom]<=Max(dates[date]),OR(tblContract[DateContractTo] >= Max(dates[date]), tblContract[DateContractTo]= 0)), 1,0))
I use for the count of active Units: CALCULATE(COUNTROWS(tblUnit), IF(AND(tblUnit[ExploitationDateFrom]<=Max(dates[date]),OR(tblUnit[ExploitationDateTo] >= Max(dates[date]), tblUnit[ExploitationDateTo]= 0)), 1,0))
I would like to count the free units (units that has no contract)
In this example is the outcome 1 (nr. 17611) on the max(dates[date]) Max(dates[date]) is here today
I would like to count the units with contract
In this example is the outcome 2 (nr. 17671 and 17650) on the max(dates[date]) Max(dates[date]) is here today
Can anyone help me?
Thanks
4 Replies
Hey DjZijlstra ,
Try creating measures as below:
Units with no contract = VAR _unit = SELECTCOLUMNS(TblUnits,"UnitID", TblUnits[UnitID]) VAR _inactivecontract = SELECTCOLUMNS(Filter(TblContract,TblContract[Actief] = 0),"UnitID",TblContract[MainUnitID]) RETURN COUNTROWS(INTERSECT(_unit, _inactivecontract))Units with contract = VAR _unit = SELECTCOLUMNS(TblUnits,"UnitID",TblUnits[UnitID]) VAR _activecontract = SELECTCOLUMNS(Filter(TblContract,TblContract[Actief] = 1),"UnitID",TblContract[MainUnitID]) RETURN COUNTROWS(INTERSECT(_unit, _activecontract))Results would be like this:
Thanks PC2790
the measure gives me a bit more info than before, but it is not yet the number that is should be. In this case and example i used 1 contract on a unit, but there can be more contracts on 1 unit in the past that can be expired. So that must not be count.
is it possible to use the date i selected (on a calender) and the explationdate from and to from the unit and contractdates?
In that case, you can add a condition in the calculation of _unit to take into consideration the dates similar to what you have done above.
See if that works for you
