Display table data in HTML Content visual

[v-tangjie-msft]

Scenario: 

In the use of Power BI Desktop, the Table visual is the first choice for most users to display data. However, there are some aspects of the Table visual that do not meet our needs well. For example, the font size cannot be changed according to the length of the word. Table visual can only set global font size. Or set the color of a part of the value according to the conditions. The conditional formatting settings of the Table visual affect the values in the entire cell. In this article, we will use DAX in conjunction with HTML to generate HTML Content visual similar to the Table visual to display data.

Expected Result:

1. The font size will change according to the length of the font

2. Color setting for a part of the result

Sample Data:

The Table data is shown below:

How:

1.Create a calculated column to calculate the total sales for each item. It is used to compare with the average sales of all products to determine whether it meets the target. Products whose total sales do not exceed the average will be marked in red.

	Total =
	CALCULATE (
	    SUM ( 'Sales_Table'[Amount] ),
	    ALLEXCEPT ( 'Sales_Table', 'Sales_Table'[Product] )
)

2. Create a new table based on this data table and add a column for calculating the length of the name.

	Table =
	ADDCOLUMNS (
	    SUMMARIZE (
	        'Sales_Table',
	        'Sales_Table'[Product],
	        "Total", SUM ( Sales_Table[Amount] )
	    ),
	    "Length", LEN ( [Product] )
)

3. Create a calculated column and use the HTML language with the table data.

Content = 
	 VAR _fontsize = 
	    SWITCH(TRUE(),
	        [Length] <=5,"5",
	        [Length] >5 && [Length] <=14,"3",
	        [Length] >14,"1"
	)
	VAR _average = 
	    CALCULATE(AVERAGE('Table'[Total]),ALL('Table'))
	VAR _color = 
	    SWITCH(TRUE(),
	        [Total] < _average,"red",
	        "black")
	RETURN 
	"<tr>" & 
	    "<td style= width:150px;><font size=" & _fontsize & ">"& [Product] & "</font></td>" &
	    "<td align=left><font size = 3>" & "Sales: </font>" & "<font color=" & _color & ">"  & [Total] & "</font></td>" &
	"</tr>"  

In the above DAX expression, the variable '_fontsize' returns different numbers depending on the length of the product name. These numbers are used to set the font size in the HTML tag '<font size = >'. You can modify this variable to change the number returned according to your needs. In the 'RETURN' part of the expression, we use HTML tags '<tr>', '<td>', '<font>'. '<font>' is used to set the font size and color. '<tr>' represents a row, '<td>' represents a cell, and the above expression represents two columns of data. If you need to add more columns, just add '<td>' before '</tr>'. 'style', 'align', 'size' are all attributes of HTML tags, which can set the tag's 'style', 'alignment', 'font size' respectively.

4. Create a measure to display the data.

Result =

VAR _content = CONCATENATEX('Table',[Content])

VAR _total = SUM('Sales_Table'[Amount])

RETURN

"<table >" &

    "<tr>" &

        "<td><font size = 6>Product</font></td>" &

        "<td><font size = 6>Sum of Amount</font></td>" &

    "</tr>" &

        "<td colspan = 2><hr style = border-color:blue;></td>" &

         _content &

        "<td colspan = 2><hr style = border-color:blue;></td>" &

    "<tr>" &

        "<td><font size = 6>Total</font></td>" & "<td align=right><font size = 6>" & _total & " </font></td>" &

    "</tr>" &

"</table>"

In the above DAX expression, we use the function 'CONCATENATEX' to concatenate the values of the column 'Content' into a text. The variable '_total' is used to achieve an effect similar to 'Total' in the table visual object. In the 'RETURN' part of the expression, make sure all the content is wrapped by HTML tags '<table>'. '<table>' is used to define a table. '<hr>' represents a straight line, and the attribute 'colspan' is used to define the number of columns that the line spans.

5. Get the Html Content visual and place the measure ‘Measure’ into it.

Now you will get the result that I show in above Expected Result part.

Summary:

If you need to create an Html Content visual again, you can simply modify the corresponding value within the Html tag. Compared to table visuals, we have more flexibility to get the styles we need. However, HTML Content visuals also have some limitations. For example, it cannot sort data by the product name in alphabetical order. It also cannot use the built-in 'sort by column' function in Power BI. You can decide which visual object to use based on your actual needs.

Additionally, please note that HTML Content visual is a third-party custom visual which is out of Microsoft support. If you require dedicated support for a particular challenge, you can visit HTML Content - Custom Visual for Power BI (html-content.com)

Author: Wenbin Zhou

Reviewer: Ula and Kerry

Display table data in html visual object.pbix