Forum Discussion

javirmerino's avatar
javirmerino
Helper III
6 years ago
Solved

Total WTD with USERELATIONSHIP

Hi guys, i've written the below bit of DAX but i'm getting a zero return for it - when i'm expecting a count of hundreds.

I'm assuming its because the relationship between my tables is based on the ReceivedDate rather than the ClosedDate, so i'm assuming there's an error in my code somewhere?

Any help at all would be appreciated on this one;

 

 

Closed WTD = 
IF (
    HASONEVALUE ( Dim_Date[Year] )
        && HASONEVALUE ( Dim_Date[WeekOfYear] ),
    CALCULATE (
        COUNT ( 'SysAid Extract'[ClosedDate] ),
        USERELATIONSHIP ( 'SysAid Extract'[ClosedDate], Dim_Date[Date] ),
        FILTER (
            ALL ( Dim_Date ),
            Dim_Date[Year]
                = VALUES ( Dim_Date[Year] )
                && Dim_Date[WeekOfYear]
                    = VALUES ( Dim_Date[WeekOfYear] )
                && Dim_Date[Date]
                    <= MAX ( Dim_Date[Date] )
        )
    ),
    BLANK ()
) + 0

 

 

Thanks in advance, all!

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    OK, here is what I *think* you were going for perhaps? Hard to tell, maybe explain what the purpose of the measure, is it only supposed to count the items closed in the current week or in all weeks previous to the current week? Not sure what you were doing with the VALUES but you can't use them like that because they return a table of multiple values. I also commented out the HASONEVALUE checks because I'm not sure what you were doing with that either.

     

    Closed WTD = 
//IF (
//    HASONEVALUE ( Dim_Date[Year] )
//        && HASONEVALUE ( Dim_Date[WeekOfYear] ),
    VAR __Date = TODAY()
    VAR __Year = YEAR(__Date)
    VAR __WeekOfYear = WEEKNUM(__Date)
RETURN
    CALCULATE (
        COUNT ( 'SysAid Extract'[ClosedDate] ), 
        USERELATIONSHIP ( 'SysAid Extract'[ClosedDate], Dim_Date[Date] ),
        FILTER (
            ALL ( Dim_Date ),
            Dim_Date[Year]
                = __Year
                && Dim_Date[WeekOfYear]
                    = __WeekOfYear
                && Dim_Date[Date]
                    <= __Date
        )
    )
//    ),
//    BLANK ()
//) + 0

20 Replies