Measure not recognizing relationship between Date table and Fact Table

DAX
1

Hi all, I have a very simple model but for some reason it is returning an incorrect value when I am trying to a simple calculation based on the Date Table filter.

I have a Many to One cardinality between the Campaigns table and the Date table. see image # 1.

For this measure, I am using the Revised MQL Date column from the campaigns table. See image #2. It is returning an incorrect total of 1,061 for Q2-2023. If I was to go to the campaigns table and manually
filter for “Y” and for date range for Q2-2023 (apr-June), I get a value of 1,153.

I am racking my head to figure out what could be the issue. I have tried modifying the measure with and without using userelationship and it still is not providing the accurate result.

What am I missing? and thanks for your help.

Image #1
image

Image #2

image
image824×598 26.2 KB

WIP Enterprise DNA - v2.pbix (15.1 MB)

2

Hi @ysherriff. The relationship is inactive so won’t filter directly (make active to filter directly).
.I’d probably start with that, and if you’re still not getting the results you want/expect, adjust your model (currently [Dates] → [Contacts] → [Campaigns]).
Greg

2 Likes
3

Hey @ysherriff

The issue is with how you had the Dates filtering the Campaigns table. 1) there are multiple inactive relationship between dates and the Campaigns table . 2) there is also an active filter, where Date filters >> Contacts on Create Date >> and only those emails that have a create date between the date period selected in your slicer get passed as a filter to Campaigns table.

This affects the output of the measure. and this will continue to be an issue.

Your measure worked just fine as soon as i broke the chain (Date filters >> Contacts Filters >> Campaign) by deleting the relationship between Dates and contact table.

image
image1195×699 21.9 KB

Id recommend that you relook at the relationships between the tables.
WIP Enterprise DNA - v2.pbix (15.1 MB)

1 Like
4

Thank you both but help me understand conceptually what the issue is with the model logic. Conceptually, it is setup correctly, right? There is only one active relationship from the date table. The campaigns table is the fact table and therefore on the many side of the relationship to the contacts, date and campaign id table. So I don’t understand, conceptually, why this would be an issue from a modeling perspective and/or best practices.

Can you enlighten me?

5

Hey @ysherriff

What in this case is causign the issue , is that Date table ( Dim table) is Filtering Contact Table ( also a dim table)
(- this is not wrong, but for what you are trying to achieve in your measure, it is giving you undesired results. - I explain that below in detail. )

but now if you also try to activate a relationship between Date Table and Campaigns table, it wont work.

  • If you Try to activate any of your inactive relationships ( between date and campaigns table) , this is what you will get

image

having said that, You need to understanding whats happening in your model.

Lets forget about all inactive relationships between Date and Campaigns table and focus on the Single Active relationship chain

Date Tabel Fiters Contact Table on Creation Date
Contact Table Filters Campaigns table on email

Because this entire chain of relationships is active, this means that even before your measure gets to work , your Campaign table already getting filtered (evaluation context) based on this chaing of filters

The measure will run its calculation on Campaign table that is filtered by contacts table, based on contacts that have their Creation date falling between the dates in the selected Dates Slcier.

As per the measure you have in palce :

Test MQL = 

CALCULATE (
    COUNTROWS ( 'Campaigns' ),
    FILTER (
        'Campaigns',
        'Campaigns'[Unique MQL per Contact per Campaign] = "Y" &&
        NOT ( ISBLANK ( 'Campaigns'[Revised MQL Date] ) ) &&
        'Campaigns'[Revised MQL Date] IN VALUES ( 'Date'[Date] )
    )
)

you only want filters on Campaigns’[Unique MQL per Contact per Campaign] and Campaigns’[Revised MQL Date] , ---->>not on the contact creation date<<----

(Look out for some content to understand the concept of Expanded Tables. - That may help you better understand this. )

As soon as you delete the relationship between dates and contact table, the chan is broken.

and this part of dax code, : Campaigns’[Revised MQL Date] IN VALUES ( ‘Date’[Date] ) gives you your desired results.

in this scenario you can activate a direct relationship between date and campaigns table or use the ( userelationship function. - That too should work.

**

I hope that helps.

Regards,
Japjeet

2 Likes
6

Thanks Japjeet. Your explanation makes sense. I have to keep in mind how the data filters first.

Much appreciated.

1 Like