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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 🙂 🙂

Anonymous
Not applicable

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors