Recently I started working in Power-BI to generate few reports. I am new to the Power BI. So far i am able to manage key task, but stuck at one point-- I have one matrix in my report which uses one measured column. I have used IF condition in that measure column, and based on this condition categorised them in 3 types. Now when i am populating these on matrix, i can see only 2 categories not 3. The reason behind this is that there is no value falling under the third category. but i want to show 3rd category as well with zero data. I have tried "Show item with no data" but no luck.Any help will be appreciated. Thanks in advance.
1 Answers
Give it a try with below steps.
- Create a separate "Categories" table with all possible categories.
- Create a relationship between the "Categories" table and your calculated column.
- Use categories from "Categories" table in the visual. Mark "Show items with no data".
Let's see a simplified example.
I have a Sales table (with very small number of rows for simplicity) like this. There are possibly 3 categories, A, B, and C. However, category C is not yet appearing in the existing data.
In my matrix visual, there is no category C, with no wonder.
Now, I create a Category table with all possible categories including C, then build a many-to-one relationship between Sales and Categories tables.
It is advised that you turn Category in Sales table ("many" side) to be hidden in report view, to make sure the users will correctly choose the one from Categories table.
Then, in the setting of the matrix visual, I replace the Category with the one in Categories table, and mark "Show items with no data".
Category C is successfully shown up in the matrix with empty value.
Related