cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karlosdsouza Regular Visitor
Regular Visitor

Relationship Dates Table

This is a simple problem but i dont know why i am not able to figure it out,

 

My data has 2 tables - one table containing the status of different stages of conversion of lead i.e. Feasibility and conversion. The other table is the calendar table that i prepared.

 

I suspect the problem is in the relationship.

 

FeasibleCount = COUNTAX(FILTER('Lead','Lead'[Feasibility]="Yes"),'Lead'[Lead ID])

LeadCount = COUNTROWS('Lead')

 

Capture.JPG

Why does "Lead Count" not give correct data when shown against Date from Calendar table.

The power BI file is enclosed.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Relationship Dates Table

Hey,

 

what you experience is the following, the direction of the relationship between both tables is mixed up.

 

image.png

 

Basically it should always be this way: on the one side the dimension table (Calendar) and on the many side the fact table (Lead).

 

After changing the direction in the relationships dialog you will face another isssue. the values of the leaddate column also contain time part, it doesn't matter that the formatting just dispplays the date part. I recommend that you create a new column, that just contains the date part, this can be done either using DAX or from within Power Query.

 

For demonstration I opted to create a DAX column using this DAX statement in the Lead table:

Lead Date just date =
DATE(
    YEAR('Lead'[Lead Date])
    ,MONTH('Lead'[Lead Date])
    ,DAY('Lead'[Lead Date])
)

And a created the relationship between this new column and the calendar table, this then leads to this:

image.png

 

Hopefully this is what you are looking for.

 

Regards,

Tom

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
1 REPLY 1
Highlighted
Super User
Super User

Re: Relationship Dates Table

Hey,

 

what you experience is the following, the direction of the relationship between both tables is mixed up.

 

image.png

 

Basically it should always be this way: on the one side the dimension table (Calendar) and on the many side the fact table (Lead).

 

After changing the direction in the relationships dialog you will face another isssue. the values of the leaddate column also contain time part, it doesn't matter that the formatting just dispplays the date part. I recommend that you create a new column, that just contains the date part, this can be done either using DAX or from within Power Query.

 

For demonstration I opted to create a DAX column using this DAX statement in the Lead table:

Lead Date just date =
DATE(
    YEAR('Lead'[Lead Date])
    ,MONTH('Lead'[Lead Date])
    ,DAY('Lead'[Lead Date])
)

And a created the relationship between this new column and the calendar table, this then leads to this:

image.png

 

Hopefully this is what you are looking for.

 

Regards,

Tom

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!