Forum Discussion

dineshj's avatar
dineshj
Helper I
4 years ago
Solved

Excluding Blanks in a Column

Hi, I have the below column I am creating in PowerBI dekstop based on another column. If the value in the cell is 0 give me On Time result. If less than 0, give me Early result and great than 1, late result. 
Final Due= if('Source1'[Final Due 1] = 0, "On Time",if('Source1'[Final Due 1] < 0, "Early", "Late"))
 
However, the blanks are showing up as On Time as well. How do I exclude the blanks so they don't show up?
Show and Tell
Tips and Tricks
  • vojtechsima's avatar
    vojtechsima
    4 years ago

    dineshj ,
    I would have to see your data, however, you can implement ISBLANK check first and modify the blanks into something else that you can then filter out.

    SWITCH(
TRUE(),
ISBLANK(Source1[Final Due 1]), "No value",
Source1[Final Due 1] = 0, "On Time",
Source1[Final Due 1] < 0, "Early",
"Late")


     

3 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    4 years ago

    Hi, dineshj 
    if you use multiple conditions, don't use IF but SWITCH:

     

    SWITCH(
TRUE(),
Source1[Final Due 1] = 0, "On Time",
Source1[Final Due 1] < 0, "Early",
"Late")

     

    and then in the Visulasiation panel and its filter, just Select all except "Blank".

    Something like this:

     

    • dineshj's avatar
      dineshj
      Helper I
      4 years ago

      Hi vojtechsima , I used the Switch function but still getting the same results. The blanks are still showing up as "On time". I looked in the transform data query and see that the original column has blanks as null.

      I am not sure why it's still taking the blanks as 0s. 

      • vojtechsima's avatar
        vojtechsima
        Super User
        4 years ago

        dineshj ,
        I would have to see your data, however, you can implement ISBLANK check first and modify the blanks into something else that you can then filter out.

        SWITCH(
TRUE(),
ISBLANK(Source1[Final Due 1]), "No value",
Source1[Final Due 1] = 0, "On Time",
Source1[Final Due 1] < 0, "Early",
"Late")