Forum Discussion

ksab23's avatar
ksab23
Helper I
3 years ago
Solved

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 t...
Need Help
Show and Tell
Tutorial Requests
  • olgad's avatar
    olgad
    3 years ago

    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

     

     

jaweher899's avatar
jaweher899
Impactful Individual
3 years ago

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.

  • ksab23's avatar
    ksab23
    Helper I
    3 years ago

    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?