Please help! why the userelationship not working

[xiumi_hou]

Dear all,

I have two dimesion table(d_date and d_cases) both link to a fact table(f_call)

Now I want to use userelationship to build realtionship between my two dimesion tables. My goal is to use d_date as a slicer to filter the report, and count how many cases completion from cases table. But is gave error: USERELATIONSHIP function can only use the two columns references participating in relationship.

Below is my dax function: 

Count_Completed_Cases =

CALCULATE(

DISTINCTCOUNT(d_Cases[CaseNumber]),

d_Cases[Closure_reason_c] = "Completed",

USERELATIONSHIP(d_Cases[Closed_date_c],d_date[Date])

)

Thank you so much!

[hnguy71]

I believe the error is stating that you need either an active or inactive relationship that already has been set.  Only then will you be able to use the USERELATIONSHIP function. What you want actually would be to use TREATAS

So in your example, something like this:

Count_Completed_Cases =

CALCULATE(

DISTINCTCOUNT(d_Cases[CaseNumber]),

d_Cases[Closure_reason_c] = "Completed",

TREATAS(VALUES(d_Cases[Closed_date_c]),d_date[Date])

)

[xiumi_hou]

Thanks for the reply. When I changed the funtion, it gave below error, could you please help me check it? 

Too many arguments were passed to the VALUES function. The maximum argument count for the function is 1.

@hnguy71 

[hnguy71]

did you forget to close the parenthesis after VALUES?

[parry2k]

@xiumi_hou can you make sure you have relationship between d_cases[closed_date] with d_date[Date]

if you already have relationship between these two tables on other column, you still need to set relationship on closed_date which will be inactive.

There can be only one active relationship, but you can have more than one inactive relationships

[xiumi_hou]

Thanks Parry, it seems I forgot to create a new relationship and then use userelationship function. 

[parry2k · Accepted solution]

@xiumi_hou so after setting the relationship, did it worked?