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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.