Forum Discussion

apuype's avatar
apuype
Frequent Visitor
3 years ago
Solved

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...
blank
USERELATIONSHIP
  • lbendlin's avatar
    lbendlin
    3 years ago

    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.

lbendlin's avatar
lbendlin
Super User
3 years ago

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.

apuype's avatar
apuype
Frequent Visitor
3 years ago

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!