cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
ksab23
Helper I
Helper I

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.

NameRandom data
Person 1XXX
Person 2XXX
Person 3XXX


Dataset 2. (Person 3 missing because they did not participate)

NameVideo titleRegistration
Person 1YYYyes
Person 2YY1yes


Desired dataset outcome

NameVideo title (YYY) Video title (YY1)Video title (YY2)
Person 1100
Person 2110
Person 3000


Any help is appreciated

1 ACCEPTED SOLUTION
olgad
Super User
Super User

Hi there, 
first replace Registration value yes with 1. 

olgad_0-1675678200168.png

then pick a video title and pivot it

olgad_1-1675678314952.png

Then merge two tables on the name

olgad_2-1675678356871.png

Then expand it 

olgad_3-1675678441076.png

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

 

 

olgad_4-1675678630725.png

 

Hope that helps

 

 


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

View solution in original post

7 REPLIES 7
v-jianboli-msft
Community Support
Community Support

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.

v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1675761895738.png

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.

olgad
Super User
Super User

Hi there, 
first replace Registration value yes with 1. 

olgad_0-1675678200168.png

then pick a video title and pivot it

olgad_1-1675678314952.png

Then merge two tables on the name

olgad_2-1675678356871.png

Then expand it 

olgad_3-1675678441076.png

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

 

 

olgad_4-1675678630725.png

 

Hope that helps

 

 


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
ITManuel
Helper V
Helper V

Hi @ksab23 ,

 

try the following steps in PowerQuery.

 

1st. Pivot the column video tile in Dataset 2 

DS2.JPG

2nd. Merge Dataset 1 with Dataset 2 

Merge.JPG

3rd. Expand video columns

Expand.JPG

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? 

jaweher899
Super User
Super User

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:

  1. 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])

  1. Next, create a relationship between Dataset 1 and the new VideoTitles table using the "Name" column as the common key.

  2. 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)

  1. 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?

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors