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 ...
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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
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
javirmerino's avatar
javirmerino
Helper III
6 years ago

Hey Greg_Deckler I managed to get your code to work simply by adding +0 to each of the dimension references, which converted them from a string into a number that can then be used as a comparison;

Closed WTD = 
    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]+0
                = __Year
                && Dim_Date[WeekOfYear]+0
                    =  __WeekOfYear
                && Dim_Date[Date]
                    <= __Date
        )
    ) +0

Thanks again!