Forum Discussion

sebastian_ebg's avatar
sebastian_ebg
Helper I
9 years ago

Multi-table DAX Attribution Model

I'm trying to generate an attribution model for our company's emails based off a few different related tables. I'm fairly new to DAX, so I've been fumbling around a bit searching for examples of how ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi sebastian_ebg,

 

I create a new calculate table to deal with your requirement, you can refer to below steps:

 

Add a measure to lineitem table to calculate the total spend per customer.

 

Total Spend = SUMX(FILTER(ALL(LineItems),[CustomerKey]=MAX([CustomerKey])&&RELATED(ProductDetails[AdvertisedFlag])=TRUE()),[Value])

 

 

Calculate table:

 

ResultTable = SELECTCOLUMNS(FILTER(MailingDataFull,[OpenCount]>0),"CustomKey",[CustomerKey],"MailingKey",[MailingKey],"Total",[Total Spend])

 

Add calculate column to calculate the average spend.

 

Average = [Total]/COUNTROWS(FILTER(ResultTable,[CustomKey]=EARLIER(ResultTable[CustomKey])))

 

Result table:

 

 

Visual:

 

 

Regards,

Xiaoxin Sheng

sebastian_ebg's avatar
sebastian_ebg
Helper I
9 years ago

Thanks so much for all your help Xiaoxin! This is so close to working now.

 

The only part that's not working is that the [Conversion Value] measure isn't obeying other filtering when applied to the result table.

 

Conversion Value = CALCULATE(SUM(LineItems[Value]), CustomerDetails[MailOpens] > 0, Attribution[DirectAttribution] = TRUE())

 

Example: If you bring the [Conversion Value] measure in as a card, and filter by "Primary" for the ProductDetails[ProductType], it properly shows $557.92. However, once applied to the ResultTable calculation, it shows the total of $1655.64, even when filtering by Primay product type.

 

If you change the [ConversionValue] measure to the following, it works fine - but I want to be able to apply the filter manually.

 

Conversion Value = CALCULATE(SUM(LineItems[Value]), CustomerDetails[MailOpens] > 0, Attribution[DirectAttribution] = TRUE(), ProductDetails[ProductType] = "Primary")

Is there any way to get it to obey filtering at the measure level, so that the measure updates based on filters without needing to hard code it?

 

Regards,

Sebastian

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi sebastian_ebg,

     

    Did you create the relationship between the result table and original table?

     

    Regards,

    Xiaoxin Sheng

  • sebastian_ebg's avatar
    sebastian_ebg
    Helper I
    9 years ago

    I can link it to the Mailing table, but not to the Customer table because it creates a circular dependancy.

     

    I don't think that would be the issue though, as the measure obeys the filtering when applied as a card, but when used within another measure/table, it ignores filtering. Reading up on it a bit, it may be something to do with cross-directional filtering, but I'm definitely out of my depth there.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi sebastian_ebg,

     

    You can try to use values function to enable the slicer affect:

     

    Conversion Value = CALCULATE(SUM(LineItems[Value]), CustomerDetails[MailOpens] > 0, Attribution[DirectAttribution] = TRUE(), Values(ProductDetails[ProductType]))

     

    Regards,

    Xiaoxin Sheng

  • sebastian_ebg's avatar
    sebastian_ebg
    Helper I
    9 years ago

    Unfortunately that doesn't have any effect. I believe it's because of the context of the calculation. I might need to leave this for a while and re-visit it with a fresh look.