cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tanat_inc
Helper II
Helper II

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

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])
)

tanat_inc_0-1620788272033.png

 

When relationship is intactive, result is as below : 

tanat_inc_1-1620788357210.png

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

tanat_inc_2-1620788419940.pngtanat_inc_4-1620788490705.png

 

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

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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])
)

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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])
)

It's work !!!

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

Thank you very much 🙂 🙂

Krutigawale33
Responsive Resident
Responsive Resident

Hello @tanat_inc ,

 

You can with lookupvalue

 

Please find the more information on the below link

https://dax.guide/lookupvalue/

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors