Dax measure stopped working

[Anonymous]

Hi guys,

My ex-collegue had made a report and included a dax measure in it.
The report was always working until today. The visuals stopped working and the measure gave an error: A table of multiple values was supplied where a single value was expected.

The measure is:

EmployeeKey =

CALCULATE(

DISTINCT('DimEmployee'[EmployeeKey]),

FILTER('DimEmployee',

'FactActualHoursBillability'[TT_EMP_ID] = 'DimEmployee'[EmployeeId] &&

'FactActualHoursBillability'[TT_DATE] >= 'DimEmployee'[RowEffectiveDate] &&

'FactActualHoursBillability'[TT_DATE] <= 'DimEmployee'[RowExpirationDate]

)

)

The measure should return a number as value and the data type is: Whole number. I've tried to change it to decimal numbers but it still doesn't work.

Can someone help me solve this puzzle? 

[Anonymous]

Hi @Anonymous ,
till now the DAX you have provided was returning a single value per row because the Filter() function was true for only one row. Maybe now after the data was refreshed, you are getting multiple rows that satisfy the Filter() condition and hence you are getting a multiple values which is the error.

[Anonymous]

I think this is the problem. How can I Fix this? 

[Whitewater100]

Hi:

Sometime you need to add a function(as first part of measure) like COUNTROWS or some other aggregation as it looks like a whole table of values is trying to fit into a scalar solution.

Try adding COUNTROWS( then what your measure says.

Hope that helps.

[Anonymous]

I've tried countrows but it still doesnt work...

[amitchandak]

@Anonymous , This is a new column, as per screenshot and code. And should work as a new column in table FactActualHoursBillability

This seems correct

EmployeeKey =
CALCULATE(
DISTINCT('DimEmployee'[EmployeeKey]),
FILTER('DimEmployee',
'FactActualHoursBillability'[TT_EMP_ID] = 'DimEmployee'[EmployeeId] &&
'FactActualHoursBillability'[TT_DATE] >= 'DimEmployee'[RowEffectiveDate] &&
'FactActualHoursBillability'[TT_DATE] <= 'DimEmployee'[RowExpirationDate]
)
)

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous · Accepted solution]

Hi Amit,

Thanks for your reply!

I think the problem is my dataset. 

What will be the best way to share my pbix with you?