Power bi show items with no data

In this Power bi tutorial, we will discuss power bi show items with no data. And also we discuss the below points:

• What is show items with no data in power bi
• What are the circumstances show items with no data does not affect
• How Power bi show items with no data works
• Power bi show items with no data as 0
• power bi show items with no data bar chart
• power bi show items with no data line chart
• Power bi show all months even if no data
• power bi show text if no data

What is show items with no data in Power bi

In Power bi when show items with no data feature get enabled, it will show the rows with empty/null/ blank values on the visualization.

What are the circumstances show items with no data does not affect

The power bi show items with no data feature don’t have the effect the following circumstances:

• When there is no measure added to the power bi visual, and grouping column come from the same table.
• When groups are unrelated in power bi.
• When power bi doesn’t run the queries for visual that have unrelated groups.
• The measure in power bi is unrealte to any of the groups, this is because the measure will never be blank for only some group combination.
• If there is user defined measure filter, that excludes blank measure in power bi.

How power bi show items with no data works

Here we will see how power bi show items with no data work in the power bi desktop.

We are using the below sample data to show items with no data.

• By using this sample data I have created a table visualization, here you can see the region name is not shown whose total quantity sold is blank.

• In this case if you want to show the region where there is no sale, for this we need to apply the show items with no data feature.
• For this click on the dropdown present in the value field, in the visualization pane.

In the below screenshot you can see the power bi show items with no data.

Power bi show items with no data as 0

Here we will see how power bi show items with no data as 0 in the matrix in power bi.

• We are using the below sample table, to show items with no data as 0 in matrix in power bi desktop.

Now we will create a table with the name column, then we will create the relationship between the original table and the calculated table.

Then we will calculate a measure to show 0 value in the matrix.

• Lets create a calculated table, so, click on Modelling tab -> New table from the ribbon.
• Then write the Dax formula:

TableName = Distinct(Table1[Name])

Now we will create a relationship between the original table and calculate table in the model tab by clicking on the manage relationship.

• Now in the report tab, click on the new measure in power bi ribbon.
• Then write the DAX formula to show 0.
• The measure is:

Measure = Count(Table1[Id]) + 0

• Select the matrix visualization from the visualization pane.
• In the rows field, drag and drop the name column from TableName table in the field pane.
• In the column field, drag and drop the status column from the Table1 in the field pane.
• In the value column, drag and drop the measure from the table1 in the field pane.

In the below screenshot you can see the Power bi show items with no data as 0.

Power bi show items with no data bar chart

Here we will see how to show month with no data in bar chart in power bi desktop.

• We are using the below sample data to show items with no data bar chart

• Then, Select stacked column chart visual from the visualization pane in power bi.
• In the Axis field i have added Date column and value field add value from the field pane.

• Then click on the drill icon to drill down to the month level, in power bi desktop.
• After the stacked column chart will show the month for the data present.

• The to show the month where the value is 0, in the axis field click on the dropdown and select Show items with no data.

Power bi show items with no data line chart

Here we will see how power bi show items with no data line chart in power bi desktop.

We will use the below sample data to create power bi show items with no data in line chart example.

• Load the data and then select line chart visual from the visualization pane.
• In the axis field, drag and drop the Id column from the field pane.
• In the value field, drag and drop Amount column from the field pane and select sum as aggregation from the dropdown.

• Now in the above screenshot you can see the, it is not showing the amount with blank value.
• For this we will create a measure to show the blank value.
• Click on the new measure from the ribbon. The measure is:

 Amount = IF(ISBLANK(SUM(SaleLine[Amount])), 0, SUM(SaleLine[Amount]))

• Now in the value field remove the Amount column and add the measure.
• Then go to Format pane, and expand the X axis, then change the type to continuous.

• In the below screenshot, you can see the power bi show items with no data line chart.

Power bi show all months even if no data

Here we will see how to show all months even if no data in power bi desktop.

We will use the below table as sample data to show all months even if no data in line chart.

• Then we will create a calculated table which fills the blank with zero.
• Click on the Modelling tab -> new table from the ribbon in power bi desktop
• Write the dax formula:

Calc Table = 

VAR GapFiller = SELECTCOLUMNS(
                GENERATE(VALUES('Table'[Device]),CALENDAR(MIN('Table'[Work Start]),MAX('Table'[Work Start]))) , 
                "Device" , [Device] ,
                --"Word Start", [Date] ,
                --"Time Running",0 ,
                "Date (Calculated)",[Date])
             
VAR C = ALL('Table'[Device],'Table'[Date (Calculated)])                
RETURN UNION(
    'Table',
    ADDCOLUMNS(EXCEPT(GapFiller,C),"T",0,"D",[Date (Calculated)])
    )

• Now we will create a line chart with continuous x-axis.
• Select the line chart from the visualization pane.
• In the Axis field, drag and drop date field from the calc table in the field pane.
• In the legend field, drag and drop device field from the calc table in the field pane
• In the values field, drag and drop Time running field from the calc table in the field pane

In the below screenshot, you can see the Power bi show all months even if no data

Power bi show text if no data

Here we will see how to show text if there is no data in the power bi desktop.

We are using the below table to show text where there is no data in the sales column.

• We will create a measure, which will show text with no data in power bi.
• For this click on the New measure from the ribbon, then write the dax formula:

Total Sale = 
Var GetSale = SUM(Salepryr[Sale])
Return
If(ISBLANK(GetSale),"No sales for this year.", GetSale)

• Select the Table visual from the visualization pane in power bi desktop.
• In the value field, drag and drop the Year column and Total sale measure from the field pane.

In the below screenshot, you can see the power bi show text if no data.

You may also like the following Power BI tutorials:

• How to use Microsoft Power BI Scatter Chart
• Power BI combine columns from two tables
• Microsoft Power BI Combo Chart
• Power Bi key influencers
• Power BI Donut Chart
• How to create a Power BI Dashboard in Microsoft teams

In this power bi tutorial, we learned power bi show items with no data. And also we discussed the below points:

• What is show items with no data in power bi
• What are the circumstances show items with no data does not affect
• How Power bi show items with no data works
• Power bi show items with no data as 0
• power bi show items with no data bar chart
• power bi show items with no data line chart
• Power bi show all months even if no datapower bi show text if no data