In this tutorial, I have explained the issue I faced when trying to display all the values in the Matrix visual that I wanted to show in my Power BI report.
The Issue is:
I have created a Matrix visual where I added data to the Rows, Columns, and Values. When I place a text field [Location] in the Values section of a Matrix visual, the matrix displays the first value, and it does not display all the locations.
From the screenshot below, you can see that the matrix is not displaying all the locations.
Solution:
Unfortunately, Power BI Matrix will display the first or last value if we are taking the Text field in the Value section.
This happens because Power BI doesn’t know how to aggregate text values, unlike numbers (such as Sum, Average, etc.), so it displays the first entry value based on the row and column combinations.
We can use the CONCATENATEX() expression to show all the values in the Power BI Matrix.
Check out Add and Customize a Dropdown Slicer in Power BI
Display All Values in Matrix Visual Power BI
Below, you can see a Matrix visual in Power BI, where the Rows include the ‘Product‘ field, the Columns include the ‘Year‘ field, and the Values include the ‘Location‘ field.
In the Matrix visual, note that it displays only the first value based on the Row and Column combination.
As you can see from the data source below, which is connected to Power BI, it displays different locations for the product along with the corresponding year.
Now, I wanted to display all the Location values in the Matrix visual. See the Image below:
For example, let’s take a Keyboard product. For this, I wanted to show all the locations present in the data source, where it has [Keyboard with 2023 Year].
Here are the steps to follow:
- On the Home tab of the Power BI report canvas, click on New Measure. It will open another window where you can add measures.
- Provide the below given expression in the Measure that will combine the values separated by a comma.
Combined Locations = CONCATENATEX(Sales,Sales[Location],",")Replace Sales, Sales[Location] from the expression above with your data names provided.
- Then, return to the Report view of the visual and drag and drop the newly created measure into the Value field from the Data pane.
- Now, you can see the matrix visual shows all the values, not just the first value. Reference the screenshot below.
This way, you can show all values for a text field, not just the first or last entries from the data connected in a Power BI Matrix.
If you find any difficulties or have questions regarding this, please leave a comment in the section below.
Look at Other Power BI Matrix Tutorials:
- Power BI Matrix Visual Conditional Formatting
- Display Power BI Matrix Visual in Tabular Format
- Sort Matrix Column Headers in Power BI
- Calculate Percentage of Total in Power BI Matrix
- Replace Blank Values with Zero in Power BI Matrix Visual
After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.
