Relationship Dates Table

[karlosdsouza]

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')

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

The power BI file is enclosed.

[TomMartens · Accepted solution]

Hey,

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

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:

Hopefully this is what you are looking for.

Regards,

Tom