topic Re: USERELATIONSHIP in a filtered measure in DAX Commands and Tips

USERELATIONSHIP in a filtered measure

Anonymous — Thu, 06 Jun 2019 21:20:36 GMT

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.

Re: USERELATIONSHIP in a filtered measure

parry2k — Fri, 07 Jun 2019 01:15:34 GMT

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

Re: USERELATIONSHIP in a filtered measure

Anonymous — Fri, 07 Jun 2019 13:43:08 GMT

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."

Re: USERELATIONSHIP in a filtered measure

parry2k — Fri, 07 Jun 2019 13:56:07 GMT

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

Re: USERELATIONSHIP in a filtered measure

Anonymous — Fri, 07 Jun 2019 14:04:43 GMT

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

Re: USERELATIONSHIP in a filtered measure

parry2k — Fri, 07 Jun 2019 14:09:29 GMT

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

Re: USERELATIONSHIP in a filtered measure

Anonymous — Fri, 07 Jun 2019 14:13:12 GMT

parry2k That's it!! Thank you!!

Re: USERELATIONSHIP in a filtered measure

Analitika — Tue, 11 Aug 2020 07:52:14 GMT

but your solution has synta errors !!!