Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
javirmerino
Helper III
Helper III

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!

1 ACCEPTED SOLUTION

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

20 REPLIES 20
Pragati11
Super User
Super User

Hi @javirmerino ,

 

Yes your USERRELATIONSHIP function is wrong as your relationship is based on RECEIVEDDATE.

For more details on user reltionship function refer this:

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Hi @Pragati11 , thanks for the feedback. However i do have a second inactive relationship between the dimensions table and the ClosedDate, which is why i'm trying to reference this also. See relationship diagram below.

 

Relationship.png

 

Is this not the correct use for it?

Hi @javirmerino ,

 

You need to have Primary relationships between the columns, in order to use USERRELATIONSHIP function.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Thanks @Pragati11, i had assumed that this would work with inactive relations also. in fact, it has with another measure in the same workbook;

 

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

 

What would you suggest as a solution for this? i'm unable to make the relationship active as i've already got the primary connection between my dimensions table and the ReceivedDate.

 

Would i need to duplicate my dimensions table in this instance?

Hi @javirmerino ,

 

If you need to use "ClosedDate" coulumn in USERRELATIONSHIP function, you need to have primary relationship between "ClosedDate" and "Dim_Date[DATE]" columns.

If you are not using "ReceivedDate" for any such related calculations, I would suggest delete "ReceivedDate" column relationship and make "ClosedDate" column relationship ACTIVE with "Dim_Date[DATE]" column.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Thanks, @Pragati11. I think you're incorrect though (sorry!). This is what i've found in the official documentation;

 

  • In USERELATIONSHIP, the status of a relationship is not important; that is, whether the relationship is active or not does not affect the usage of the function. Even if the relationship is inactive, it will be used and overrides any other active relationships that might be present in the model but not mentioned in the function arguments.

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

 

Hi @javirmerino ,

 

Apologies on that, I think I missed this in the documentation for USERRELATIONSHIP. Thanks for correcting me on this.

Can you just try one more thing? Try making your relationship bi-directional.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Not a problem @Pragati11  - i never like pointing out errors when im being helped!

 

No change though, i'm afraid. i was hoping that would be it 😀

Hi @javirmerino ,

 

Can you attach a sample pbix file for this issue?

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!

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.

 

image.png

 

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.

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

OK, so I managed to get this functioning. I blasted your Dim_Date table and recreated it with CALENDAR and then recreated some columns and got all of the measures working. First impression, your first HASONEVALUE is causing your measure to be zero. Your Dim_Date table has more than one year and more than one week of year so it just skips the calculation and returns BLANK() + 0.

 

Looking at what I can do to correct the calculation now.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @javirmerino ,

 

Looks like this pbix uses some connection to database and I can't open it at my end.

 

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Greg_Deckler
Super User
Super User

Tough to say without something to test with. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

But I'd be willing to bet that your USERELATIONSHIP is in the wrong place?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks, @Greg_Deckler. Do you mind if i PM you my .pbix file to look into further, briefly?

 

i'd already tried putting the USERELATIONSHIP in pretty much every other part of the query. This was the only place that didnt give me a syntax error!

Sure.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.