Force table to show blanks

Question

Hi everyone,&nbsp;I have a problem with the table visual in PowerBi Desktop.&nbsp;Do anybody know if you can force it to show blanks?&nbsp;My example is this:&nbsp;If you have a general ledger with 12 accounts. Revenue, production costs, contribution margin etc. but lets say nothing has been charged on one of the accounts in a given month. Then this account will no longer be shown in the table if you slice on this particular month. Is there any way to force the table to show the account even though it is blank??&nbsp;Best regards,&nbsp;/Rasmus&nbsp;&nbsp;&nbsp;

greg_deckler · Answer

You could try this, create a column like:

Column = 1

Add that to your table visualization and then drag the column width so that it disappears. Since that column will always contain a value all of your table rows should display in the table.

nhallquist · Answer

Greg_Deckler&nbsp;had a good solution on a different post. &nbsp;In the Data section, create an IF statement that uses&nbsp;ISBLANK to find the Blank field, and puts in "$0" where it is blank, ELSE the field value. &nbsp;&nbsp;That should work as well.&nbsp;Nate

sean · Answer

Rasmusrock&nbsp;I know exactly what your talking about - and it is a source of big frustration...&nbsp;You want to force ALL dates to show (whatever they may be in the Calendar) regardless whether there's an entry or not&nbsp;Greg_Deckler's&nbsp;solution works but it has very limited use&nbsp;(I&nbsp;use column = 0 because in a stacked column chart&nbsp;- it adds nothing)&nbsp;As soon as you try to break down the data in a table or a visualization it again removes the dates where there are no entries&nbsp;In the picture you can't place any field in the Column Series (in the Chart) and you can place another field in the table but then the dates with no entries disappear...&nbsp;&nbsp;EDIT: Try creating a Measure that simply sums this column = 0 and then add&nbsp;this Measure&nbsp;to your table/matrixThen drag with the mouse to hide it as in the picture...This seems to help&nbsp;with the Matrix&nbsp;but not with the Charts :smileysad:&nbsp;&nbsp;&nbsp;

rasmusrock · Answer

Hi&nbsp;Sean,&nbsp;Thanks for the input, however, it does not solve my problem.. I have put a bit more work into it, and hardcoded my data into PowerPivot in Excel, to check if it works here - and it does!!&nbsp;My output when i insert a table in PowerBi Desktop:&nbsp;And my output in excel:&nbsp;&nbsp;Exactly the same tables, relations and measures are used in both scenarios.. So does anybody have a clue on why it shows data in the 'dækningsbidrag', 'driftsresultat' and 'resultat før skat' rows in Excel and not in PowerBI Desktop when i slice the data on year and month?&nbsp;Best regards,&nbsp;/Rasmus