cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pragati11 Resident Rockstar
Resident Rockstar

Re: Total WTD with USERELATIONSHIP

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

javirmerino Helper III
Helper III

Re: Total WTD with USERELATIONSHIP

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 😀

Pragati11 Resident Rockstar
Resident Rockstar

Re: Total WTD with USERELATIONSHIP

Hi @javirmerino ,

 

Can you attach a sample pbix file for this issue?

 

Thanks,

Pragati

javirmerino Helper III
Helper III

Re: Total WTD with USERELATIONSHIP

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

Pragati11 Resident Rockstar
Resident Rockstar

Re: Total WTD with USERELATIONSHIP

Hi @javirmerino ,

 

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

 

 

Super User IV
Super User IV

Re: Total WTD with USERELATIONSHIP

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: Total WTD with USERELATIONSHIP

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

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

javirmerino Helper III
Helper III

Re: Total WTD with USERELATIONSHIP

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.

 

Highlighted
Super User IV
Super User IV

Re: Total WTD with USERELATIONSHIP

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

javirmerino Helper III
Helper III

Re: Total WTD with USERELATIONSHIP

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors