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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
karlosdsouza
Helper II
Helper II

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
TomMartens
Super User
Super User

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

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

1 REPLY 1
TomMartens
Super User
Super User

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

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.