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.

power bi show items with no data works
power bi show items with no data works
  • 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.
power bi show items with no data works
power bi show items with no data works
  • 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.
power bi show items with no data works
power bi show items with no data works

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

power bi show items with no data works
power bi show items with no data works

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.
power bi show items with no data as 0
power bi show items with no data as 0

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

See also  How to Check Condition Contains String in Power Automate?

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.

power bi show items with no data as 0
power bi show items with no data as 0
  • 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
power bi show items with no data as 0
power bi show items with no data as 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.
power bi show items with no data as 0
power bi show items with no data as 0

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

power bi show items with no data as 0
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
power bi show items with no data bar chart
power bi 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.
power bi show items with no data bar chart
power bi show items with no data bar chart
  • 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.
power bi show items with no data bar chart
power bi show items with no data bar chart
  • 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 bar chart
power bi show items with no data bar chart

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.

See also  How to Create a Document Library in Power Automate?

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

power bi show items with no data line chart
power bi show items with no data line chart
  • 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.
power bi show items with no data line chart
power bi show items with no data line chart
  • 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.
power bi show items with no data line chart
power bi show items with no data line chart
  • In the below screenshot, you can see the power bi show items with no data line chart.
power bi show items with no data line chart
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.

Power bi show all months even if no data
Power bi show all months even if no data
  • 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
Power bi show all months even if no data
Power bi show all months even if no data

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

Power bi show all months even if no data
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.

See also  Power BI Slicer Dropdown

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

power bi show text if no data
power bi show text if no data
  • 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.
power bi show text if no data
power bi show text if no data

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

power bi show text if no data
power bi show text if no data

You may also like the following Power BI tutorials:

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
>