topic Re: USERELATIONSHIP & Convert BLANKS to 0 in Report Server

USERELATIONSHIP & Convert BLANKS to 0

apuype — Fri, 09 Sep 2022 12:29:29 GMT

Hello All! Looking for some guidance on converting blanks to 0 within a measure that also uses the USERELATIONSHIP function. My regular ways do not seem to work with this.

My "master measure" as I call it starts with the relationship function - this count keeps 0's as blanks but I require the 0. I use the master within the custom date period measures

Order Count Dispatch User =
CALCULATE (
DISTINCTCOUNT ( 'Executive_Reporting'[Order_Id] ),
USERELATIONSHIP ( Executive_Reporting[dispatcher_user_id], 'Master - User ID'[User ID] ))

To encorporate into our custom date periods I then move to this measure

Order Count Dispatched By Current Week CY =
VAR CurrentWeek = SELECTEDVALUE ( 'Dim_Date'[SLUSAWeekNum] )
VAR CurrentYear = SELECTEDVALUE ( 'Dim_Date'[SLUSAYearNum] )

RETURN
    IF (HASONEVALUE ( Dim_Date[SLUSAWeekNum] ),
        SUMX (
            FILTER (
                ALL ( Dim_Date ),
                Dim_Date[SLUSAWeekNum] = CurrentWeek && Dim_Date[SLUSAYearNum] = CurrentYear),
            [Order Count Dispatch User]),
        BLANK ())

You can see when brought into the table that the blanks then don't allow the calculations to work and show the proper change over prior week

Any help would be appreciated!

Re: USERELATIONSHIP & Convert BLANKS to 0

lbendlin — Sun, 11 Sep 2022 18:04:54 GMT

You have a couple of options. My preference is COALESCE but you can also add 0 to a measure that you supsect might return BLANK()

One slightly unrelated note:

IF (HASONEVALUE ( Dim_Date[SLUSAWeekNum] ), SUMX ( FILTER ( ALL ( Dim_Date ), Dim_Date[SLUSAWeekNum] = CurrentWeek && Dim_Date[SLUSAYearNum] = CurrentYear), [Order Count Dispatch User]), BLANK ())

doesn't really need the "Else" statement as BLANK is implied.

Re: USERELATIONSHIP & Convert BLANKS to 0

apuype — Mon, 12 Sep 2022 12:42:59 GMT

Thank you, I have not worked with COALESCE before - glad to learn new functions! I gave it a shot in my first measure - it worked in subbing the blanks for 0's but seemed to break the USERELATIONSHIP piece. That function seems to be a picky one in terms of what it works well with.

I did previously try adding 0 to all versions of the measure but again it then looses the relationship piece.

I have since created a measure summing my two columns and excluding the sums of 0. Not exactly the way I wanted to go around it but seems to be working for this purpose.

Thank you again for your help! Going to play with COALESCE some more for future measures. Also agreed on your else remark - I keep the same format for a bunch of measures as we use alot of custom date periods so sometimes it applies and sometimes not so I didn't even notice it there. Thank you!

Re: USERELATIONSHIP & Convert BLANKS to 0

v-henryk-mstf — Tue, 13 Sep 2022 02:48:14 GMT

Hi apuype ,

Whether the advice given by lbendlin has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry