Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Return a BLANK instead of a 0

  • 1.  Return a BLANK instead of a 0

    Posted Sep 08, 2021 10:55 AM
    Edited by Wouter Hupkes Sep 13, 2021 08:56 AM

    Hi all,

    I'm looking for a way to return a BLANK instead of a 0, based on the condition of another column. I do have 2 columns: one column contains the strings A, B and C, and the other column contains different values. However, when the string in the first column is A, then the value in the other column is always 0.

    I'm trying to figure out how I can show a BLANK in the visual "Table" when the condition of A in the first column is met, so whenever column 1 has an A, there should be a BLANK in the visual for column 2 instead of 0, otherwise the value must be shown. I thought the following would do the job, but still 0's are shown. I'm operating in Direct Query mode.

    ValueTest = IF ('Table'[Column1]="A",BLANK(),'Table1'[Value])

    Anyone any idea...?



    ------------------------------
    WP
    ------------------------------


  • 2.  RE: Return a BLANK instead of a 0

    Posted Sep 09, 2021 11:17 AM
      |   view attached

    I just did this with some sample data and got it to work. In a table I added Column 1 and ValueTest. I had to use selectedvalue and sum to get the fields to work in the IF statement.

    Value Test = IF(SELECTEDVALUE('Table'[Column1]) = "A", BLANK(),SUM('Table'[Column2]))
    Then in the visualization section, I right clicked on Column 1 and clicked "show items with no data" so that "A" would still show in the table.
    Hope this helps.


    ------------------------------
    Christopher Wright
    Sales Data Analyst
    ------------------------------



  • 3.  RE: Return a BLANK instead of a 0

    Posted Sep 10, 2021 09:21 AM
    Would this give you what you are looking for. I created a table with 2 columns and then added a calculated column to display the value of column2 unless column1 is an A then display a blank.



    ------------------------------
    Julie Hills
    Sr BI Architect
    ------------------------------



  • 4.  RE: Return a BLANK instead of a 0

    Posted Sep 13, 2021 08:58 AM
    Hi, thank you for your reply!

    It seems like a proper solution, however, SELECTEDVALUE is not a valid function in Direct Query... 

    ------------------------------
    Wouter Hupkes
    633733708
    ------------------------------



  • 5.  RE: Return a BLANK instead of a 0

    Posted Sep 12, 2021 01:10 PM
    Edited by Wouter Hupkes Sep 13, 2021 09:08 AM
    Thank you for your message!

    Unfortunately, this one doesn't do the job. It is exactly what I'm trying to do (calculated column, same formula).

    ------------------------------
    WP
    ------------------------------



  • 6.  RE: Return a BLANK instead of a 0

    Posted Sep 13, 2021 11:16 AM
      |   view attached

    Oh I'm sorry, I missed the part where you said you were in the Power Query Editor(Direct Query).

    In that case then all you need to do is go to "Add Column" tab on the top, and select "Conditional Column". Name it, and set it up how I have in the picture below. 



    ------------------------------
    Christopher Wright
    Sales Data Analyst
    ------------------------------