Skip to main content
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

Regular Visitor

USERELATIONSHIP needs selection to trigger correct calculation of measure? What am I missing?


This might be a stupid question, but I seem to have a general misunderstanding when using Measures, Filters and the DAX USERELATIONSHIP function.

I seems to work just fine for my dataset, but I always need to make a selection thus filter the data for which it then evaluates the measure using USERELATIONSHIP correctly. If I unselect everything it seems to ignore the USERELATIONSHIP calculation and gives the results without it.


I just have 2 tables with an inactive relationship:


My measure is showing distinct values as such:


distinct 2s also in 1 =
DISTINCTCOUNT('Table (2)'[number 2]) ,
'Table'[number 1],
'Table (2)'[number 2]
My expected outcome here would be 2, yet it shows as 3. 
It is counting the number 2 value '9' which is not in Table 1.


If I make a selection in the number 1 table it shows the values correctly as only '1'


How can I get the result of '2' for my measure posted above that works without a selection?

I uploaded a very simple pbix file to show what I mean.

Thanks for your help.

Community Support
Community Support

Hi @systemic_till ,

You can add write measure to calculate table2 records with if statement to compare current row contents with tbale1 records:

Measure =
IF (
    SELECTEDVALUE ( 'Table (2)'[number 2] ) IN VALUES ( 'Table'[number 1] ),
        COUNT ( 'Table (2)'[number 2] ),
        USERELATIONSHIP ( 'Table'[number 1], 'Table (2)'[number 2] )


If above not help, please explain your requirement more clarify with the expected result.


Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-pn ,
Thanks for you answer.
It didn't clarify my issue though.

In my setup, everything works as expected, if I make a selection.
Only when nothing is selected I am getting confusing results.

My expected result is 2, when nothing is selected.
Yet, I get a result of 3 as if the USERELATIONSHIP filter was missing entirely.

So, to clarify:

// Measure 1

no relationship = CALCULATE (DISTINCTCOUNT('Table (2)'[number 2])   
==> that gives 3. That's ok.

//Measure 2
distinct 2s also in 1 =

CALCULATE (DISTINCTCOUNT('Table (2)'[number 2]) ,
'Table'[number 1],
'Table (2)'[number 2]

==> that also gives 3 when no selection is made. Why is it not 2?

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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


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