Forum Discussion

AG_55's avatar
AG_55
Frequent Visitor
4 years ago
Solved

COUNTROWS with condition

Hi everyone! The task is to build a plan for recruiting group based on the entries in table. They open a vacancy by adding a new row and set estimated hire date under internal SLA and fill hire date...
Need Help
Tips and Tricks
  • AG_55's avatar
    AG_55
    4 years ago

    Got a solution. At first, we need to set 2 inactive relationships in model:

    • 'Calendar'[Date] -> 'Regions'[Estimated hire date]
    • 'Calendar'[Date] -> 'Regions'[Hire date]

    And then we have the measure:

     

    RT = 
CALCULATE( COUNTROWS('Regions'),
    FILTER( VALUES('Regions'[Estimated hire date]), 'Regions'[Estimated hire date] <= MAX( 'Calendar'[Date] ) ),
    FILTER( VALUES('Regions'[Hire date]),
        OR('Regions'[Hire date] >= MIN( 'Calendar'[Date] ),
           'Regions'[Hire date] = BLANK() ) )
)

     

    'Calendar'[Date] should be put in visualization to build dates context:

     

    Thus we already have 3 rows for March, they are for vacancies that were not closed but opened in Dec, Jan or Feb.

     

     

AG_55's avatar
AG_55
Frequent Visitor
4 years ago

Got a solution. At first, we need to set 2 inactive relationships in model:

  • 'Calendar'[Date] -> 'Regions'[Estimated hire date]
  • 'Calendar'[Date] -> 'Regions'[Hire date]

And then we have the measure:

 

RT = 
CALCULATE( COUNTROWS('Regions'),
    FILTER( VALUES('Regions'[Estimated hire date]), 'Regions'[Estimated hire date] <= MAX( 'Calendar'[Date] ) ),
    FILTER( VALUES('Regions'[Hire date]),
        OR('Regions'[Hire date] >= MIN( 'Calendar'[Date] ),
           'Regions'[Hire date] = BLANK() ) )
)

 

'Calendar'[Date] should be put in visualization to build dates context:

 

Thus we already have 3 rows for March, they are for vacancies that were not closed but opened in Dec, Jan or Feb.