Why do some rows in my table disappear when I add my measure?

[HamidBee]

HI, 

When I add my measure for some strange reason rows just disappear. How do I avoid this from happening?. I even included an index column to avoide this from happening.

 

Please find a link to the dataset that I am working with:

https://www.mediafire.com/file/f4tyos1nq8sgvtx/Sample_Statement.pbix/file

Thank you,

[edhans]

Because the measures are returning blank, so data will not show up. So go into your measure and wrap it with COALESCE. 

So SUM(Table[SomeColumn]) becomes COALESCE(SUM(Table[SomeColumn]), 0)

So if it returns blank, it will show 0, not nothing.

I didn't download your file because it is asking for eleventy three cookies and some agreement and I closed it. If my answer above isn't correct, please reshare with OneDrive or Dropbox, or maybe someone else will agree to everything that site wants to put on their computer.

[HamidBee]

Thank you for your reply. Although, I'd like the rows to show blank if there isn't a value there. Please see an image of what the table looks like before the measure is added:

As soon as I enter it this happens:

I was hoping that adding the indexes would prevent this. The index and the attribute are both set to 'don't summarize'.

I was actually implementing the same method I had learnt from an online course. The teacher used the following code:

Selected Year Actuals =

VAR CurrentItem = SELECTEDVALUE( 'Income Statement Template'[Items (Normalized)] )

RETURN

SWITCH(TRUE(),

CurrentItem = "Total Revenues", DIVIDE([Revenues],1000,0),

CurrentItem= "Total COGS",DIVIDE([COGS],1000,0),

CurrentItem="Total Gross Profit",DIVIDE([Gross Profit],1000,0),

CurrentItem= "Gross Profit %",FORMAT([Gross Profit],"0.00%"),

CurrentItem="Total Other Expenses",DIVIDE([Other Expenses],1000,0),

CurrentItem="Total Net Profit",DIVIDE([Net Profit],10000,0),

CurrentItem="Net Profit %",FORMAT([Net Profit Margin],"0.00%"),

CALCULATE([Actuals (,000)],FILTER('Income Statement Data','Income Statement Data'[Expense Items]=CurrentItem)))

To obtain the following:

  

Here one can clearly see that the blanks are included. Allbeit if I set the 'income statment item' to 'don't summarize' the blank rows become hidden.

 

On my visual, If I change the attribute from 'don't summarize' to 'first' it shows some of the empty rows but not all of them:

 

Do you have any idea on what I'm doing wrong here?

[edhans · Accepted solution]

The index doesn't matter. Powre BI only shows dimensions when there is also data (not when you just drop in a dim field and nothing else, but as soon as you drop in a measure, it filters the DIM field.)

So you'd have to create a measure that returned either 0 or a non-blank, so space or dash.

In a Table visual, you can tell it to show items with no data. Right-click on the dimension in the field pane:

[HamidBee]

Nice. The 'show items with no data' worked as it should. Thank you.