How you will display “Data not exist” message when data not available in Power BI chart visual? In this post I will show you how you can achieve this.
Let’s get started-
Sample dataset as below for this practical-
Table name – SalesData
Before moving further let’s have a look without message how visual look like with blank data.
In dataset there is no sale for year – 2012, 2013 & 2014. In Year slicer we select Year- 2012 and you can see the card visual output, it is showing blank text.
Now we will implement some custom text message if data not available, follow these steps-
Step-1: Load above sample dataset into Power BI and take two visuals-
- Card visual – Drag Sale column
- Slicer – Drag year column
Step-2: Create a measure for custom text if sales is blank, for this write below DAX code.
TotalSales_1 = Var GetSale = SUM(SalesData[Sale]) Return If(ISBLANK(GetSale),"No sales for this year.", GetSale)
Now drag this measure to card visual, and see the output-
Change Text message font color “Red” when no data exist-
Step-1: Create one new measure and write below DAX code-
Change color = IF(ISBLANK(SUM(SalesData[Sale])), "#FF0000", --Red color "#008000"-- Green color )
Step-2: Select card visual > go to format bar > under Data label, click on Fx icon.
Step-3: Color Conditional formatting dialogue box opens then select format type- Field value > then select measure in Based on field drop-down > click on OK button.
Step-4: Now select year 2012 from year slicer and see the output-
Implement the same for other visuals-
Step-1: Take clustered column chart into Power BI report page and drag year & sales columns into fields section.
Step-2: Create a measure for custom message, write below DAX.
TotalSales_2 = Var GetSale = SUM(SalesData[Sale]) Return If(ISBLANK(GetSale),"No sales for this year.", "")
This DAX is slightly different from above card visual DAX. IF sale is Blank then it will returned message else nothing.
Step-3: Now Drag one card visual top of clustered column chart and drag this measure to card visual fields section.
Turned off card visual Title, border, background & category properties.
Step-4: Now select card visual and go to format bar and click on Fx icon.
Step-5: Color dialogue box opens, then select format type- Field value > then select measure(change color) in Based on field drop-down > click on OK button (Ref :- see step no 2 & 3 of card visual).
Step-6: See the output-
Refer more DAX functions- DAX Tutorials
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.
How to display Card “Truly Blank Text Field” when (Blank) until selection is made and / or when Multi-selections have been made? “Region Name”
Let’s say we are copy pasting city names from excel directly into Power BI custom filter box to display respective city revenue. In this scenario, how to display a message “Data unavailable for X, Y, Z” cities?
How do you make it to show those years with no sales data as 0 on the graph?
Can we display a text message Loading & Loaded while loading icon rotates in Power BI dashboard.