USERELATIONSHIP in a filtered measure

[Anonymous]

I need to create a measure that calculates daily goals over time based on the number of active employess. I have an inactive relationship between 'Employees'[StartDate] and 'Date'[Date]. I have tried using the following:

Daily_Goal = 

VAR EmpCount = CALCULATE(COUNTROWS('Employees'),

                                                 'Employees'[StartDate]<='Date'[Date],

                                                  USERELATIONSHIP( 'Employees'[StartDate], 'Date'[Date]))

RETURN

20 * EmpCount

But I receive an error that "the expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

Is there a way to calculate this goal using an inactive relationship? I am unable to make this relationship active as I have a different table with an active relationship that links between the 'Date' table and the 'Employees' table.

[parry2k]

@Anonymous hmm not full sure but try following:

 
Daily_Goal = 
VAR EmpCount = CALCULATE(COUNTROWS('Employees'),
FILTER( Employees,
                                                 'Employees'[StartDate]<='Date'[Date]
),
                                                  USERELATIONSHIP( 'Employees'[StartDate], 'Date'[Date]))
RETURN
20 * EmpCount

[Anonymous]

@parry2k  thank you but unfortunately this solution gives an error message that "A single value for column 'Date' in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

[parry2k]

@Anonymous sorry, change Date[Date] to MAX( Date[Date] )

[Anonymous]

Adding MAX does get rid of the error but only calculates for the dates linked between the two tables

For example, the following in the Employee Table

Employee      StartDate

Employee1    1/2/2019 

Employee2    1/2/2019

Employee3    1/5/2019

The measure returns the following if I drop it in a table with continuous dates

Date                 Daily_Goal

1/2/2019          40

1/3/2019          NULL

1/4/2019          NULL

1/5/2019          20

The output I am looking for is

Date                 Daily_Goal

1/2/2019          40

1/3/2019          40

1/4/2019          40

1/5/2019          60

[parry2k · Accepted solution]

@Anonymous seems like I'm missing something here. seems like you are looking running total

Daily_Goal = 
VAR EmpCount = 
CALCULATE(COUNTROWS('Employees'),
USERELATIONSHIP( 'Employees'[StartDate], 'Date'[Date]))
FILTER( ALL(Date ),
Date[Date] <= MAX( Date[Date]
),
RETURN
20 * EmpCount

[Analitika]

but your solution has synta errors !!!

[Anonymous]

@parry2k That's it!! Thank you!!