Forum Discussion

DjZijlstra's avatar
DjZijlstra
Frequent Visitor
4 years ago

count rows with 2 tables

Hi there,   I have 2 tables: TblUnits                     UnitID BusinessUnitID BuildingID UnitStatusID UnitCode UnitShortName UnitTypeID ExploitationDateFrom ExploitationD...
Need Help
PC2790's avatar
PC2790
Community Champion
4 years ago

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:

 

 

  • DjZijlstra's avatar
    DjZijlstra
    Frequent Visitor
    4 years ago

    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? 

    • PC2790's avatar
      PC2790
      Community Champion
      4 years ago

      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

      • DjZijlstra's avatar
        DjZijlstra
        Frequent Visitor
        4 years ago

        Thanks. I have found the solution for this problem whit help from this community.