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

     

     

v-jianboli-msft's avatar
v-jianboli-msft
Community Support
3 years ago

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.