Forum Discussion
Matrix with several columns
Hi All,
I have been trying to figure out a correct approach to my matrix and I cannot make it work.
I would need to showcase a list of all users (rows, info from dataset 1), with the training codes list (training users took) being complete or incomplete (columns, info from dataset 2). Bear in mind that dataset number 2 involves information about users who took training (complete or in progress, both marked as 1), those who didn't start any training are not present in dataset 2 at all. It is fairly simple, I can get it done in Excel but due to volumes, I need to work with power bi as excel crashes almost constantly.
The issue starts the moment I want to report on chosen training codes only, e.g. training x1, x2, x3; so I filter them out and users who did not take any of x1, x2, x3 training, so didn't participate, do not show at all. Again, their names are not present in dataset 2 because the report is counting only people who registered/started/finished the training. I have their names in dataset 1 and I would need to see their names with incomplete status (0 or blank is good enough) after filtering out the training codes.
I tried to work around this and add an additional column with generic information that is always there in order to keep the user list intact, however, then the training codes columns disappeared altogether even though they are still in the 'build visual' column part. I played with values, columns, and rows but I really cannot figure out how to keep the user list intact, while showcasing the training status for those who didn't take it.
Any help is appreciated
2 Replies
ksab23 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.sorry, new to this, let me try to make it clearer.
dataset 1 - contains a complete user listdataset 2 - contains training codes; a user list of active participants only
I want to see a complete user list (not just active participants) while filtering out specific training codes (e.g. from training codes list x1-x100, I want to see only training codes x1-x3), and even non-active users to remain in my matrix. this is my expected outcome - showcasing all users, even those who did not take any traininguser x1 x2 x3 user1 1 1 1 user2 1 user3 and I am getting this instead
user x1 x2 x3 user1 1 1 1 user2 1 I need to see the complete user list, even those who didn't take any part in x1-x3.
as mentioned before, I tried to find something in my dataset that I could add as a foolproof check column, data that is not related to any of this but contains any value that is always present, thus keeping the user list unchanged because it is the same for all of them. but if I add a training codes column to 'columns' (so I have 'generic data' and 'training codes'), it is not visible in the matrix and I end up with this (information on x1-x3 missing).user generic data user1 yes user2 yes user3 yes
