Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
5 years ago

SUM with multiple Filters and USERELATIONSHIP

I have a simple transactional table that has an inactive relation to our calendar.

Now I would like to get the SUM of a specific column based on two conditions/ filters that also use USERELATIONSHIP.

This is my original function without USERELATIONSHIP:

SUM = 
CALCULATE (
    SUM ( 'Sales'[Quantity] ),
    FILTER (
        'Sales',
        'Sales'[Type] = 4
            && 'Sales'[Quantity] > 1
    )

Adding USERELATIONSHIP is not that simple because it is not working:

SUM = 
CALCULATE (
    SUM ( 'Sales'[Quantity] ),
    FILTER (
        'Sales',
        'Sales'[Type] = 4
            && 'Sales'[Quantity] > 1
    ), USERELATIONSHIP( 'Sales'[Date], 'Calendar'[Date])

 

What should I change here? 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    joshua1990 , It should have worked. Try with this modification

     


    SUM =
    CALCULATE (CALCULATE (
    SUM ( 'Sales'[Quantity] ), USERELATIONSHIP( 'Sales'[Date], 'Calendar'[Date])),
    FILTER (
    'Sales',
    'Sales'[Type] = 4
    && 'Sales'[Quantity] > 1
    ))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    What error are you getting? If you are using the exact formula which you posted with USERELATIONSHIP(), you have missed a closing parenthesis in the end. Try putting a closing parenthesis in the end and try again.

     

    Regards,

    Aditya