Forum Discussion

DjZijlstra's avatar
DjZijlstra
Frequent Visitor
4 years ago
Solved

Count rows with different tables

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? 

 

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

4 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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's avatar
      DjZijlstra
      Frequent Visitor
      4 years ago

      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

      176232862526319574148982176718-3-2022null25988361
      1757928625237195481532721765022-2-2022null25860271
      1732628625118192821508111761122-12-202125-3-202225438900
      176242862526419575148983176718-3-2022null25988361
      1762528625265195751489841765022-2-202121-2-202225860270

       

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        4 years ago

        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.