Tables & Relationship: Value for empty IDs when no rows are present

[markhollandau]

Hi,

I have 2 tables - one Ad, the other Conversions. Each row on Conversions will have an associated row on the Ads table. Not all Ads will have an associated Conversions row. 

Here's my starting point:

There are 3 Ad Groups from the Ads table. Total Impressions and Total Clicks come from the Ads table. Total Conversions comes from the Conversions table. You'll see that one of the Ad Groups doesn't have Conversions. This is the expected behaviour/layout.

When I add in the field Conversion ID from another table, this is the what happens, as expected:

The Ad Group that doesn't have Conversions disappears. This is now what I'm looking for. I want someone to be able to see Total Impressions across all Ad Groups, even if one doesn't have Conversions. Whereas this behavious reduces down the Total Impression metrics. 

I'd like the Conversion ID value for the missing ID to show "No Conversions". But this doesn't appear as a row in the data.

How can I add this into my data to make this happen? Do I need to add a placeholder row into the Conversions table where ID = "No Conversions" and reference this in a formula?

I would really appreciate some help, I can't figure out how to do this.

Thanks,

Mark

[jgeddes]

Depending on how your model is structured you may be able to use a measure to get the conversion id instead of using the column.
Something like 

Conversion ID = 
IF(
    ISBLANK(LOOKUPVALUE(idTable[Conversion ID], idTable[Ad Group Name], SELECTEDVALUE(adTable[Ad Group Name]))),
    "No Conversions",
    LOOKUPVALUE(idTable[Conversion ID], idTable[Ad Group Name], SELECTEDVALUE(adTable[Ad Group Name]))
)

would work. The 'SELECTEDVALUE(adTable[Ad Group Name])' would need to be the column that is populating the rows of your visual. The idTable is the table where the conversion ids are stored.

Hope this gets you pointed in the right direction. 

[markhollandau]

HI @jgeddes ,

There could be multiple conversion IDs for an Ad Group. It looks like this only works if there's only one conversion ID per Ad Group?

[jgeddes]

You are correct. LOOKUPVALUE will only work with unique values. That is my oversight. 
What table are the conversion IDs in?

[markhollandau]

Conversion ID is in the Conversion Table. Everything else should be in the Ads table.

Thanks.

[jgeddes]

Ok. Thanks for the clarification. Are you needing to do this in DAX or would a Power Query solution work for you?

[markhollandau]

At this stage I'll try both!

[jgeddes · Accepted solution]

In Power Query, in the Conversion Table you can add rows of Ad Group Names that appear in the Ad Table but not in the Conversion Table and then assign the 'No Conversions' to the Conversion Id. The existing relationships in your model should then bring in the 'No Conversions' when you use that column.
The function to add to your Conversion Table would be 

Table.Combine(
        {
            #"Changed Type", 
            Table.FromList(
                List.RemoveMatchingItems(
                    adTable[Ad Group Name], 
                    #"Changed Type"[Ad Group Name]
                ), 
                Splitter.SplitByNothing(), 
                type table [Ad Group Name = text]
            )
        }
    )

#"Changed Type' is the previous step in the Conversion Table

adTable[Ad Group Name] must be your Ad table name and the Ad Group Name column 

This should give you the additional rows in your Conversion Table which you could then do a replace values on the null values in the Conversion Id column changing them to 'No Conversions.'

EDIT: Make sure to replace the null values in the conversion column with 0.

Hope this works for you.

[markhollandau]

@jgeddes ,

Just one last little thing. It's pretty much working, but I'm now seeing a blank rows for each Ad Group in the table:

I would like to only show a blank row if there are no Conversion IDs, highlighted in yellow. I think it's showing a blank rows as there could be days where there are no conversions?

What do you think?

Mark

Thanks,

Mark

[jgeddes]

Those rows are showing because the measures for impressions and clicks have values for them. You can edit the impressions and clicks measures to show only when there is also conversions. Or filter them out with the filter pane.

[markhollandau]

Thanks @jgeddes ,

That's pretty much solved it. I do have some additional blank rows that I may need to either filter out or replace with values.