Forum Discussion
Create new dataset with blanks
Hi All,
I was tasked to create a rather complex report that would showcase all users from a given list that participated in any of our offered videos.
However, I was given two datasets - one that contains all users, and one that contains video titles and only users who took part in one or more of them.
What I would like to do is to create a dataset with users as rows, video titles as columns, and values to be 1 for participation, and 0 for nothing. Ideally, this would update itself and I want it to be a dataset, not a visual. Examples below.
Dataset 1.
| Name | Random data |
| Person 1 | XXX |
| Person 2 | XXX |
| Person 3 | XXX |
Dataset 2. (Person 3 missing because they did not participate)
| Name | Video title | Registration |
| Person 1 | YYY | yes |
| Person 2 | YY1 | yes |
Desired dataset outcome
| Name | Video title (YYY) | Video title (YY1) | Video title (YY2) |
| Person 1 | 1 | 0 | 0 |
| Person 2 | 1 | 1 | 0 |
| Person 3 | 0 | 0 | 0 |
Any help is appreciated
Hi there,
first replace Registration value yes with 1.then pick a video title and pivot it
Then merge two tables on the name
Then expand it
Then replace null in all columns by referencing that table
let
Source = #"Table",
ReplaceNulls = Table.TransformColumns(Source,{},(x) => Replacer.ReplaceValue(x,null,0))
in
ReplaceNullsHope that helps
7 Replies
Hi there,
first replace Registration value yes with 1.then pick a video title and pivot it
Then merge two tables on the name
Then expand it
Then replace null in all columns by referencing that table
let
Source = #"Table",
ReplaceNulls = Table.TransformColumns(Source,{},(x) => Replacer.ReplaceValue(x,null,0))
in
ReplaceNullsHope that helps
To achieve this desired outcome, you can use a Power BI DAX formula to create a new calculated table based on the two existing datasets. Here's an example of how you can create this new calculated table:
- First, create a unique list of all video titles from Dataset 2. You can use the DAX formula "Distinct" to do this:
VideoTitles = DISTINCT(Dataset2[Video title])
Next, create a relationship between Dataset 1 and the new VideoTitles table using the "Name" column as the common key.
Create a new calculated table using the DAX formula below:
DesiredDataset = SUMMARIZE(Dataset1, Dataset1[Name], "Video title (YYY)", IF(COUNTIF(Dataset2, [Name]=Dataset1[Name]&& [Video title]="YYY")>0, 1, 0), "Video title (YY1)", IF(COUNTIF(Dataset2, [Name]=Dataset1[Name]&& [Video title]="YY1")>0, 1, 0), ... repeat for all video titles)
- In the new calculated table, the "Name" column will be your rows, the video titles will be your columns, and the values will be either 1 or 0 based on whether the user participated in the video or not.
Note: Make sure to replace "YYY" and "YY1" with the actual names of the video titles in your datasets.
Hello, thanks! But unfortunately, I got stuck on the second step, when I create a distinct list of Video Titles I get back only that one column and thus cannot create a relationship between Dataset 1 (Names) and Video Titles, as there are no names of users. Maybe I am just not understanding properly here, could you please elaborate on that?
Hello, this seems to work somewhat but I don't think it is good for my needs, as my datasets consist of way too many rows. The list of unique users is 20k+ rows and the one with video titles and respective registrations is even longer, as users can participate in one or more videos. Thus the merged dataset takes ages to load outside of the power query and any action, such as filtering, takes way too long.
Do you perhaps know if there is a way to tweak this buffering?
Hi ksab23 ,
Use PowerQuery could avoid manually creating columns for video titles one by one. thus if your video title column has many different values, this could be a huge and tedious work.
If you need Dax, I approve with ITManuel. But it seems that creating relationships between tables is not necessary, you can try a similar DAX:
Table = SUMMARIZE ( 'Dataset 1', [Name], "YYY", IF ( CALCULATE ( COUNT ( 'Dataset 2'[Registration] ), FILTER ( 'Dataset 2', [Name] = EARLIER ( 'Dataset 1'[Name] ) && [Registration] = "Yes" && [Video title] = "YYY" ) ) >= 1, 1, 0 ), "YY1", IF ( CALCULATE ( COUNT ( 'Dataset 2'[Registration] ), FILTER ( 'Dataset 2', [Name] = EARLIER ( 'Dataset 1'[Name] ) && [Registration] = "Yes" && [Video title] = "YY1" ) ) >= 1, 1, 0 ) )Final Output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi ksab23 ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
