Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.