Forum Discussion

javirmerino's avatar
javirmerino
Icon for Helper III rankHelper 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
javirmerino's avatar
javirmerino
Icon for Helper III rankHelper III
6 years ago

i've just sent you a link via DM. i'll keep working on it at this end and hopefully we meet in the middle.


A

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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
    Icon for Helper III rankHelper 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!

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Yeah, I would guess that this is in your date table somewhere. I had to blast yours and recreate it since it was a live connection. It is likely your Year or WeekOfYear columns

  • javirmerino's avatar
    javirmerino
    Icon for Helper III rankHelper III
    6 years ago

    Thanks Greg_Deckler , i appreciate you sticking with it.

     

    To explain my intentions a little clearer, I have one measure, [Closed], with the below code used to identify all closed records as a count of [ClosedDate];

    Closed =
CALCULATE (
    COUNTAX (
        'SysAid Extract',
        [ClosedDate]
    ),
    USERELATIONSHIP ( 'SysAid Extract'[ClosedDate], Dim_Date[Date] )
)

    My intention is to have two measure based on this one to calculate the Closed YTD for all records Closed this year, and Closed WTD for all records closed this week only. The YTD measure works perfectly well  but the WTD doesnt.

     

    For your reference, the original code is a bastardisation of something i found here and have tried to fit into my model accordingly. Its not providing an error - but its not returning any values at all.

     

     

    I've just given your revised code a try but i'm still getting an error message. However, its given an error that suggests a data type mismatch?

     

    Error Message:
MdxScript(Model) (101, 13) Calculation error in measure 'Σ Measures'[Closed WTD]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

     

  • javirmerino's avatar
    javirmerino
    Icon for Helper III rankHelper III
    6 years ago

    Cheers, Greg_Deckler. You're right; week number is stored as string rather than an integer. I've raised it with our data warehouse guys to get this amended.

     

    i've tried changing the data type but as its a live connection it would change my data source to an extract (which i dont want), so i'll wait to hear back on that as i'm pretty confident this is the issue, too. Embarassed it took so long to notice but you live and learn, eh!

     

    Once confirmed its all working, i'll ensure that you get credited for the solution. Thanks again, Greg.