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(tblContrac...
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.

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.

    • DjZijlstra's avatar
      DjZijlstra
      Frequent Visitor
      4 years ago

      Thanks v-kkf-msft .

      Thats what i'm looking for.