Do not show anything in a table cell if value is zero

[Anonymous]

I have a table view with multiple columns. Several of these columns have numerical values. What I want is for the cells to show as empty, when their value is zero. If I apply the following filter:

Show Items when the value: is greater than 0

Nothing is displayed in the table. Any ideas?

[edhans]

You need to create measures and not use the fields in your table, which is bad practice anyway.

So instead of using the Sales field from your table, create this measure

Total Sales =
VAR SalesAmount =
    SUM( 'Sales Table'[Sales] )
VAR Result =
    IF(
        SalesAmount = 0,
        BLANK(),
        SalesAmount
    )
RETURN
    Result

So if sales is zero, this will return blank. Then it will not show up.

[edhans · Accepted solution]

You could write it as a single measure/line like you would an excel formula:

Total Sales = IF( SUM( 'Sales Table'[Sales] )= 0, BLANK(), SUM( 'Sales Table'[Sales] ) )

I think that is harder to read, and the Total Sales calculation (summing of the [sales] column has to be done twice) but it is a little less scary if you've never used DAX before. 

My original measure just assigned the Total Sales calculation so I could use it as many times later as I wanted to without the DAX engine recalculating it each time. Then I assigned the actual formula I wanted to the Result variable. I do that because if my measure isn't working, I can replace the very last line that returns "Result" to "TotalSales" for example so I can see what the intermediate steps are.

Then I use www.daxformatter.com to make it easier to read.

[Anonymous]

Thank you. I re-did the view, to replace 0s with a NULL. I am trying to include as little as possible, and if possible no DAX code at all and make sure that everything I need is in the SQL views. This solved the problem.

The DAX code is straightforward, thank you.

[edhans]

Great @Anonymous - glad it worked for you.

Don't be afraid to use DAX, and certianly don't avoid it. SQL Views can replace 99.9% of what Power Query does, but very little of what DAX can do. It is always best practice to use explict measures in visuals. When you just drag a numeric field to a visual, you are creating an implicit measure. 

A few advantages of an explict measure:

• You can name the measure what you want and retain that name in the visual. You don't have to rename the measure in the visual to customize it. You can, but you do not have to.
• If you apply conditional formatting to an implicit measure, then, in cases like yours, decide you need an explict measure to do the IF(0,blank,0) logic, when you remove the implicit measure and replace with the explicit measure, your formatting is lost. If you had started with an explicit measure, you just change the formula and your formatting is retained.
• You can create multiple measures that are the same formula if you need to show formatting differently. Each measure can retain its own format. (Percent with 0 places on a summary page, Percent with 2 places on a detail drill through report for example.
• There are some things you cannot do in SQL Views without creating way WAY more columns than are necessary, all of which work in DAX - it is what DAX was designed for:
  • cumulative totals
  • Percent of total
  • counting aggregations
  • SUMIFS logic (via SUMX with FILTER)
  • any kind of date intelligence (YTD, MTD, Same period last year, etc.)
  • all of those would either require more columns for the same data, or simply cannot be done.

Use SQL Views and/or Power Query for data modeling. Save the analysis for DAX. 

I'm to the point I don't even use implicit measures in scratchpad pages where I am messing around. Because invariably I want to tweak something and implicit measures are untweakable. So I take the time to write the simpliest of measures explicitly. Total Sales = SUM(Table[Sales]) then drop that in my ad hoc visual.

I then go a step further and hid all values from the model before publishing so only my key fields used for slicers, filters, etc. are available, and measures. No values.

[Anonymous]

OK, what you're saying makes sense.  What's a good source for learning DAX?

[edhans]

In my opinion, this is the best book for getting started with DAX. SuperCharge Power BI. On a scale of 1-10 of difficulty, it will take you from 1 to 5 or 6 as you go through it.

To go from 5-10 (or 11!) it would be the Definitive Guide to DAX. But do not start with this book. You'd be lost by page 10 until you get your head around basic concepts.

[Anonymous]

I am brand new to PowerBI. I assume what you posted is DAX, and I have yet to write a single DAX line of cod.e