Forum Discussion

systemic_till's avatar
systemic_till
Regular Visitor
6 years ago

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

Hey,

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 =
CALCULATE (
DISTINCTCOUNT('Table (2)'[number 2]) ,
USERELATIONSHIP (
'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.


Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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] ),
    CALCULATE (
        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.

    Regards,

    Xiaoxin Sheng

    • systemic_till's avatar
      systemic_till
      Regular Visitor
      6 years ago

      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]) ,
      USERELATIONSHIP (
      'Table'[number 1],
      'Table (2)'[number 2]
      ))                                                                                           

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