USERELATIONSHIP in CALCULATE function returning wrong results

[user248]

Hello everyone,

I am new to DAX and Power Pivot, I apologize in advance if there is a similar thread I did not see. I am using Excel 2016, I need to use USERELATIONSHIP in the CALCULATE function to use an inactive relationship in the data model (I need it for a slicer).

I am using this formula "=CALCULATE([Count of Fiscal Week Identified],USERELATIONSHIP(DAS_Pipe[Fiscal Week Identified],Week[Week]))" to create a calculated field I need to add to a pivot table.

I just need Excel to generate the pivot table on the left in the screenshot using a calculated field inserted in values, and the field 'week' in rows (having an inactive relationship in the data model). When I tried using the formula above, I get the table on the right which is completely different from the table on the left, and I cannot figure out why... I need this to include the pivot table in a slicer. Any help would be very much appreciated. Thank you in advance!

Regards,

 

[user248]

Hello again,

After digging into it, I found out the USERELATIONSHIP function is not working... The relationship does exist (see the data model), I set them both to "inactive". Does anyone know why the function is not working?

This is the formula I am using to calculate the measure: = CALCULATE(COUNT(DAS_Pipe[Fiscal Week Identified]),USERELATIONSHIP(DAS_Pipe[Fiscal Week Won],Week[Week]))