Distinct count of two column with summarize does not work with userelationship()

[tanat_inc]

Hi community, 

I use DAX code and relation ship below to try to distinctcount by HN and Date. The calculation work well if I have active relationship between  d_date_table[Date]  and  f_visit_info[Date].

Problem is : When I switch relationship between  d_date_table[Date]  and  f_visit_info[Date] to inactive and use userelationship() as the code below, it's does not work.

Campus Visit =

  VAR _table = SUMMARIZE('f_visit_info','f_visit_info'[visit_date],"distinctcount",DISTINCTCOUNTNOBLANK('f_visit_info'[hn]))

RETURN  CALCULATE(

  SUMX(_table,[distinctcount]),

  USERELATIONSHIP('d_date_table'[Date],f_visit_info[visit_date])

)

When relationship is intactive, result is as below : 

While the same code with active relationship provides the right result as below : 

How should I rewrite the code to be able to inactivate relation and use "userelationship()" fuction in measure instrad.

[amitchandak · Accepted solution]

@tanat_inc , Try a measure like

Campus Visit =
VAR _table = SUMMARIZE('f_visit_info','f_visit_info'[visit_date],"distinctcount",calculate(DISTINCTCOUNTNOBLANK('f_visit_info'[hn]),
USERELATIONSHIP('d_date_table'[Date],f_visit_info[visit_date])))
RETURN CALCULATE(
SUMX(_table,[distinctcount])
)

[tanat_inc]

It's work !!!

You solved 3 of my issues already in this month. 
That's really save my day.

Thank you very much 🙂 🙂

[Anonymous]

Hello @tanat_inc ,

You can with lookupvalue

Please find the more information on the below link

https://dax.guide/lookupvalue/

[tanat_inc]

Thanks friend, this should be a useful alternative work around.