Forum Discussion

Promethes_2's avatar
Promethes_2
Helper II
9 years ago
Solved

USERRELATIONSHIP With SUM and Filter Question

Hello,

 

I have the following tables:

 

'Calendar'[Date]  1 to M -> 'Sales'[Posted Date]

 

My calendar table has a calculated column [Is Current Week] that holds a value of 1 if true if the date falls within the current week period I've defined.

 

I've been trying to write a DAX expression that will return the total sales using USERELATIONSHIP based on a inactive relationship between Calendar[Date] and Sales[Order Date].  I can get a total with this expression for a cummulative value:

 

     CALCULATE( SUM('Sales'[Amount]), USERELATIONSHIP ( 'Calendar'[Date], 'Sales'[Order Date] ))

 

However, when I try to filter for only sales for the current week, my expression returns nothing.  I've tried a few permutations but always end up with a blank result.   Here is my latest attempt:

 

     CALCULATE(CALCULATE( SUM('Sales'[Amount]), USERELATIONSHIP ( 'Calendar'[Date], 'Sale'[Order Date] )),  FILTER(All 

     ('Calendar'),'Calendar'[Is Current Week] = 1))

 

Anyone have any pointers on how or if I can accomplish this?  I have reviewed this link:

 

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

 

But can't quite seem to get things working with the filter.

 

 

 

 

  • How about this version.  It should work:

     

    Measure =
    CALCULATE (
        SUM ( 'Sales'[Amount] ),
        USERELATIONSHIP ( 'Calendar'[Date], 'Sale'[Order Date] ),
        'Calendar'[Is Current Week] = 1
    )

3 Replies

  • How about this version.  It should work:

     

    Measure =
    CALCULATE (
        SUM ( 'Sales'[Amount] ),
        USERELATIONSHIP ( 'Calendar'[Date], 'Sale'[Order Date] ),
        'Calendar'[Is Current Week] = 1
    )
    • Promethes_2's avatar
      Promethes_2
      Helper II

      It actually all boiled down to a misconception that I had transactions in the current week when I did not.  Thank you for your reply, I marked your answer as the solution as it does work.  I

      • mattbrice's avatar
        mattbrice
        Solution Sage

        Good to hear as that was my next suggestion.  I coulnd't see an issue with the measure based on facts given.