Forum Discussion

id013's avatar
id013
Helper V
6 years ago

Anyone having issues using USERELATIONSHIP and filters?

So I have a table that shows product sales. There is a purchase date and a return date as well as a field that states whethere they are within 30 days of purchase date, there is also a "Within 30 Days" slicer that I can use to filter the data by that field. So I have 2 relationships between my sales table and the date dimension:

purchase_date_key = date_key

return_date_key = date_key

 

The primary relationship is purchase date 

 

So I have 2 measures:

count_purchases := CALCULATE(DISTINCTCOUNT(order_id),ISBLANK(purchase_date)=FALSE())

count_returns := CALCULATE(DISTINCTCOUNT(order_id),ISBLANK(purchase_date)=FALSE(),ISBLANK(return_date)=FALSE(),USERELATIONSHIP(return_date_key,date_key)

 

Both seem to work fine but when I want to count the number of returns within 30 days of purchase the filter doesn't seem to work properly.

 

If I do:

CALCULATE(DISTINCTCOUNT(order_id),ISBLANK(purchase_date)=FALSE(),ISBLANK(return_date)=FALSE(),USERELATIONSHIP(return_date_key,date_key,FILTER(sale,within_30_days=TRUE)) it returns blank (it shouldn't)

 

If I do:

CALCULATE(DISTINCTCOUNT(order_id),ISBLANK(purchase_date)=FALSE(),ISBLANK(return_date)=FALSE(),within_30_days=TRUE,USERELATIONSHIP(return_date_key,date_key) it returns the right count but when I set the within_30_days slicer to FALSE the count doesn't decrease. 

 

Is there another way I can accomplish this? 

 

 

4 Replies

    • id013's avatar
      id013
      Helper V

      hi amitchandak 

      Unfortunately I don't have control over the fields and the within_30_days field is actually just a True\False value that gets updated. 

       

       

       

      • amitchandak's avatar
        amitchandak
        Super User

        What exactly you want to do with that 30 day filter. Can you explain with example

         

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi id013 ,

     

    It's hard to find the solution only by these formulas. Can you show some sample data to us if you don't have any Confidential Information?

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

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