Forum Discussion

suzsword's avatar
suzsword
Frequent Visitor
7 years ago
Solved

USERELATIONSHIP help - measure not using the relationship

I have a problem with the context being lost when calculating measures. I've attached the relationship that is being used.

Relationship

I have two measures (Today's Value and Yesterday's Value) created that include USERELATIONSHIP so that the inactive relationship is used.

 

 

Today's Value = CALCULATE(SUM('pds V_BOPD'[BOPD]),
FILTER('pds V_BOPD', 'pds V_BOPD'[full_date]=DATEVALUE("21-feb-2019")), USERELATIONSHIP( 'pds V_BOPD'[field] , DimFieldList[Fieldname])
)

I then created another measure that indicates whether there is an increase or decrease in values and shows a coloured circle depending on the value. 

 

Arrows = SWITCH(
    TRUE(),
    ISBLANK([Yesterday's Value]), BLANK() & "Missing Data",
    ISBLANK([Today's Value]), BLANK() & "Missing Data", 
    [Today's Value] = [Yesterday's Value], CONCATENATE(UNICHAR(9898) ," No Change"), 
    [Today's Value] > [Yesterday's Value], CONCATENATE(UNICHAR(128309), " Increase"), 
    [Today's Value] < [Yesterday's Value], CONCATENATE(UNICHAR(128308), " Decrease"), 
    BLANK() )

As soon as I add the Arrows measure, the results are basically showing a cartesian product, where each field is showing against each site.  What am I missing?

 

Correct Results - without Arrows Measure

 

 
 
 
 
 
 
 
 
 
Results when Arrows measure is used - every field is showing with every site
  • suzsword  if you remove the site code, it removes the cartesian, the issue is because you dont have a relationship on the table that has site code, therefore it returns everything

4 Replies

    • suzsword's avatar
      suzsword
      Frequent Visitor

      You should be able to get the file from Here

       

       

      Changing the order of the USERELATIONSHIP function made no difference. 


      Thanks

      Susan

      • vanessafvg's avatar
        vanessafvg
        Community Champion

        suzsword  if you remove the site code, it removes the cartesian, the issue is because you dont have a relationship on the table that has site code, therefore it returns everything