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
apuype
Frequent Visitor

USERELATIONSHIP & Convert BLANKS to 0

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 

apuype_0-1662726097906.png

 

Any help would be appreciated! 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

3 REPLIES 3
v-henryk-mstf
Community Support
Community Support

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

lbendlin
Super User
Super User

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.

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! 

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.