Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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
ReplaceNulls
Hope that helps
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.
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 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
ReplaceNulls
Hope that helps
Hi @ksab23 ,
try the following steps in PowerQuery.
1st. Pivot the column video tile in Dataset 2
2nd. Merge Dataset 1 with Dataset 2
3rd. Expand video columns
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?
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:
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)
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |