Count rows with different tables

[DjZijlstra]

Hi there,

I have 2 tables:

and 

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? 

[v-kkf-msft]

Hi @DjZijlstra ,

Please try the following measures:

count of active contracts = 
CALCULATE (
    COUNTROWS ( TbIContract ),
    FILTER (
        TbIContract,
        AND (
            TbIContract[DateContractFrom] <= MAX ( 'dates'[date] ),
            OR (
                TbIContract[DateContractTo] >= MAX ( 'dates'[date] ),
                TbIContract[DateContractTo] = 0
            )
        )
    )
)

count of active Units = 
CALCULATE (
    COUNTROWS ( TbIUnits ),
    FILTER (
        TbIUnits,
        AND (
            TbIUnits[ExploitationDateForm] <= MAX ( dates[date] ),
            OR (
                TbIUnits[ExploitationDateTo] >= MAX ( dates[date] ),
                TbIUnits[ExploitationDateTo] = 0
            )
        )
    )
)

count the units with contract = 
VAR tab_contract =
    FILTER (
        TbIContract,
        AND (
            TbIContract[DateContractFrom] <= MAX ( 'dates'[date] ),
            OR (
                TbIContract[DateContractTo] >= MAX ( 'dates'[date] ),
                TbIContract[DateContractTo] = 0
            )
        )
    )
VAR tab_units =
    FILTER (
        TbIUnits,
        AND (
            TbIUnits[ExploitationDateForm] <= MAX ( dates[date] ),
            OR (
                TbIUnits[ExploitationDateTo] >= MAX ( dates[date] ),
                TbIUnits[ExploitationDateTo] = 0
            )
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( TbIContract ),
        tab_contract,
        tab_units,
        USERELATIONSHIP ( TbIUnits[UnitID], TbIContract[MainUnitID] )
    )

free units = [count of active Units] - [count the units with contract]

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DjZijlstra]

Thanks @v-kkf-msft 

That kind of measure i had to try earlier, but that was not the number i was looking for. 

I have a file added (table copied below), where 1 unit has 2 contracts, and a unit has 2 contracts (1 active and 1 not active)

So if i count the contracts with these measures there a 3 contracts and 3 units, so 0 free. But thats not correct. There is 1 free. 

What measure can i use to get this number?

ContractBusinessUnitIDContractNoCustomerIDContactIDMainUnitIDDateContractFromDateContractToObjectIDActief

17623	286	25263	19574	148982	17671	8-3-2022	null	2598836	1
17579	286	25237	19548	153272	17650	22-2-2022	null	2586027	1
17326	286	25118	19282	150811	17611	22-12-2021	25-3-2022	2543890	0
17624	286	25264	19575	148983	17671	8-3-2022	null	2598836	1
17625	286	25265	19575	148984	17650	22-2-2021	21-2-2022	2586027	0

[v-kkf-msft · Accepted solution]

Hi @DjZijlstra ,

This is because I did not de-duplicate the TbIContract[MainUnitID] column, please try the modified measure:

count the units with contract = 
VAR tab_contract =
    FILTER (
        TbIContract,
        AND (
            TbIContract[DateContractFrom] <= MAX ( 'dates'[date] ),
            OR (
                TbIContract[DateContractTo] >= MAX ( 'dates'[date] ),
                TbIContract[DateContractTo] = 0
            )
        )
    )
VAR tab_units =
    FILTER (
        TbIUnits,
        AND (
            TbIUnits[ExploitationDateForm] <= MAX ( dates[date] ),
            OR (
                TbIUnits[ExploitationDateTo] >= MAX ( dates[date] ),
                TbIUnits[ExploitationDateTo] = 0
            )
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT(TbIContract[MainUnitID]),
        tab_contract,
        tab_units,
        USERELATIONSHIP ( TbIUnits[UnitID], TbIContract[MainUnitID] )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DjZijlstra]

Thanks @v-kkf-msft .

Thats what i'm looking for.