Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dax Lookup Column Behaving Incorrecly

Hello! 

 

Example file with Sales & Mailers Tables

 

Background:

I have 2 tables:

  • Mailer table – telling us who we physically mailed a marketing campaign to (as well as details on the mailer)
  • Sales Table – telling us who actually bought our widget

Problem:

I'm looking to add a "Last Attributed Template Type" from our Mailer table into our Sales Table. Right now there’s no column in our Sales table that says which Template resulted in a sale.

 

I need to add a “Last attributed Template” column in my Sales Table so I can look at (Count of template type mailed [Mailers Table]) /(Count of template type resulting in a sale [Sales Table]) – to get conversion by template type.  

 

@parry2k  was awesome and helped me to get the right attribution for the "Last Attributed Campaign", I was later asked to also add last attributed for "Template Type", and others - all of which worked with that formula except for the "Last Attributed Template Type".

 

For example customer MB000139332 is incorrectly pulling from a different row in the Mailers Table that all the other "Last Attributed" Columns are pulling from - should be template "E" as opposed to "H"

 

How can I get it so that "Last Attributed TemplateType" also pulls from the same row as the other Last Attributed Columns - not sure why it's behaving differently?

 

5 REPLIES 5
HotChilli
Super User
Super User

Each of the calculated columns calls MAX on a text field.

In the case of 1393332, there are two records for the getLatestCampaignDate (both on 1st March 2018).  The DAX code for the calculated columns returns the larger of the column required (so it'll be the later in alphabetical order)

For the templateType, 'H' is bigger than 'E' so that gets returned.

For the % likely to buy, '81-90' is bigger than '31-50' so that gets returned.

 

That's why it's happening.  You'll have to decide how to return the correct fields if there are two records for the same date.

Anonymous
Not applicable

Thank you @HotChilli !

 

So I basically want the same values as shows up for the "Last Attributed Campaign", is there a way for me to just get the Deal type to return the corresponding template that is on the same row as the "Last Attributed Campaign"?

"Last Attributed Campaign" works on the same basis i.e. uses MAX on a text column.  Usually one formulates a business rule to decide which fields to select and return. Is this possible?

Anonymous
Not applicable

Hi @HotChilli - First off, thank you so much!!

 

The business is fine with even returning the first non-blank value since it's essentially a tossup to attribute a sale to 1 mailer when 2 or more were sent in the same month.

 

I think maybe swapping out Max in this case for first non-blank would handle it.

 

If I leave the "getlatestcampaigndate" variable as a max, but swapped Max for firstnonblank for variable after that for all the "Last Attributed columns"- they should all pull from the same row correct? And still pull from the most recent column based on the criteria we laid out earlier (still march in the problem example we had earlier)?

 

On a side note - is there a resource I might be able to look at different business rule options? I have a feeling that the business would like to know what their options are.

Anonymous
Not applicable

@HotChilli - I did test adding the firstnonblank for Last Attributed Templatetype, and though that worked correctly, when I changed the other "Last Attributed" columns to firstnonblank, it ended up changing to different columns again, so unless I just change the Templatetype column to first-nonblank, that seems to make it work in this case, though not sure if that logic is the best way to ensure that the values are pulling from the same column.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors