Forum Discussion

steambucky's avatar
steambucky
Helper III
8 years ago
Solved

Converting null values to -1 inside my formulas...

I have have made a some formulas that tecnically work BUT  I relize its not reporting the correct information.

 

I am trying to calculate how many months ist been since some data has been reviewed. 

 

This formula works but i know its where i intruduce my first problem.

 

_Months_of_age = DATEDIFF(MAX(SIR_XLS[Rev_Date]),TODAY(),MONTH)

 

The problem is i have some null values in there - for some reason this wasnt creating any errors, so i kept on going with my next calculated colum.

 

This formula breaks the months this up into 4 different catecories: 

 

_Months since reviewed 2 =
IF (
    (SIR_XLS[_Months_of_age_2])< 13,
    "12 or less",
    IF (
        AND ( (SIR_XLS[_Months_of_age_2])> 12, [_Months_of_age_2] < 25),
        "12 – 24",
 IF (AND ((SIR_XLS[_Months_of_age_2])> 26, [_Months_of_age_2] < 61),
 " 25 –  60", 
        "61 + "
    )
))

 

 

 

 

 

 

What I need it s catecory for null - At the moment is bulks out my "12 months or less" category because somehow I have confused it -  is treating null as 0

 

I am a bit confused where and how I tell it about null values.  Its import we know what percentage are null values  AND how old things are. I thought if I could make null or isblank = -1 then I would be able to make this work....

 

Any help would be MOST welcome. I Need a Power BI Hero! :)

 

Pointers? Directions? Thoughts?

 

Kindest regards,

 

Marc.

 

 

 

 

 

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi steambucky,

    What I need it s catecory for null - At the moment is bulks out my "12 months or less" category because somehow I have confused it -  is treating null as 0

     

    If I understand your requiremently that you want to have five categories,"null", "12 or less",  "12 – 24",  " 25 –  60",  "61 + ".

     

    If it is , you could modify your formula  like below.

     

    Column = 
IF (
     'Table1'[_Months_of_age] =BLANK(),
    "null",
    IF (
        ( 'Table1'[_Months_of_age] ) < 13,
        "12 or less",
        IF (
            AND ( ( 'Table1'[_Months_of_age] ) > 12, [_Months_of_age] < 25 ),
            "12 - 24",
            IF (
                AND ( ( 'Table1'[_Months_of_age] ) > 26, [_Months_of_age] < 61 ),
                " 25 -  60",
                "61 + "
            )
        )
    )
)

    And the output is below.

    If you need additional help please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

     

     

    Best Regards,

    Cherry

1 Reply

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi steambucky,

    What I need it s catecory for null - At the moment is bulks out my "12 months or less" category because somehow I have confused it -  is treating null as 0

     

    If I understand your requiremently that you want to have five categories,"null", "12 or less",  "12 – 24",  " 25 –  60",  "61 + ".

     

    If it is , you could modify your formula  like below.

     

    Column = 
IF (
     'Table1'[_Months_of_age] =BLANK(),
    "null",
    IF (
        ( 'Table1'[_Months_of_age] ) < 13,
        "12 or less",
        IF (
            AND ( ( 'Table1'[_Months_of_age] ) > 12, [_Months_of_age] < 25 ),
            "12 - 24",
            IF (
                AND ( ( 'Table1'[_Months_of_age] ) > 26, [_Months_of_age] < 61 ),
                " 25 -  60",
                "61 + "
            )
        )
    )
)

    And the output is below.

    If you need additional help please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

     

     

    Best Regards,

    Cherry