Forum Discussion

PowerBIWhat's avatar
PowerBIWhat
Helper I
4 years ago
Solved

USERELATONSHIP help

Hi,

 

I have created a measure that looks like

 

Measure = CALCULATE(
COUNT('Product Schedule'[Product ID]),
USERELATIONSHIP('Product Schedule'[Shipping date],'Date table'[FullDate])
)
 
I'm using it in a report page with many visuals all filtered on Full date which I have as a slicer for the user to change. I want the measure to count the product id's based on the full date selected. There is another required relationship which is why I need the USERELATIONSHIP to do this.
 
The problem I have is that when I filter on a full date, it only filters items that were shipped on the first of the filtered month rather than what I want which is all shipped items for the filtered month.
 
Thanks in advance
 
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi PowerBIWhat ,

    According to your description, I create a sample, the relationship of Shipping date and FullDate is inactive.

     

    This is the measure code:

    Measure = CALCULATE(
COUNT('Product Schedule'[Product ID]),
USERELATIONSHIP('Product Schedule'[Shipping date],'Date table'[FullDate])
)

    When I don’t put measure into the product visual, it only show the first product as you say.

     

     

    When I put them in the same visual, it get the correct result, I think it’s because the relationship is only active with the measure, so it has to be putted in.

    I attach my sample below for reference.

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • mh2587's avatar
    mh2587
    Super User
    4 years ago

    CALCULATE(count([product id]), USERELATIONSHIP(productschedule[shipping date], Date table[FullDate]))

    • PowerBIWhat's avatar
      PowerBIWhat
      Helper I
      4 years ago

      This didn't work. Does it make a difference using only the column name in the count?

      That is the only difference I could spot. Unless I'm being blind.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi PowerBIWhat ,

    According to your description, I create a sample, the relationship of Shipping date and FullDate is inactive.

     

    This is the measure code:

    Measure = CALCULATE(
COUNT('Product Schedule'[Product ID]),
USERELATIONSHIP('Product Schedule'[Shipping date],'Date table'[FullDate])
)

    When I don’t put measure into the product visual, it only show the first product as you say.

     

     

    When I put them in the same visual, it get the correct result, I think it’s because the relationship is only active with the measure, so it has to be putted in.

    I attach my sample below for reference.

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.