How to join properly

[PBI5851]

Hello, 

 I have a hurdle to overcome. I dont have control on the Database so cannot create new table or modify the underlying structure. 

I have the date table, and Table A which has a createddate, and table B which has an actiondate. 

Table A

AccountID

createddate

Table B

CustomerID

CustomerName

AccountID

CustomerActionDate

Date to Table A is 1-many

Table A to B is 1-many 

I cannot join Date table to Customer table via date - customeractiondate as that would create a loop. 

On the report,, i have a time slicer on the date table

i have to create multiple cards within the same page for count of Accounts created and countof customers in a specific time period.  Now when i change the time slicer the number of Accounts created are correct, but the customers are incorrect, which makes sense due to join. 

Please advise on how to achieve this and even if it is possible ?

[parry2k · Accepted solution]

@PBI5851 you should create a relationship between date table and table b, it will be inactive relationship and create a measure with the following expression.

Customer Count = CALCULATE ( COUNTROWS ( CustomerTable ) ,  USERELATIONSHIP ( CustomerTable[CustomerActionDate], DateTable[Date] ) ) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[V-pazhen-msft · Accepted solution]

@PBI5851 

Try remove the "Table A to B is 1-many" relationship and make both Tables relate with Date Table via the date columns. 

Date Table =>Table A (1- many)

Date Table =>Table B (1-many)

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@PBI5851 , it is a direct query or live mode? If it import mode create a date table join with date in both tables. Also, make dates in table do not have a timestamp. else create a new date only column.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[parry2k · Accepted solution]

@PBI5851 you should create a relationship between date table and table b, it will be inactive relationship and create a measure with the following expression.

Customer Count = CALCULATE ( COUNTROWS ( CustomerTable ) ,  USERELATIONSHIP ( CustomerTable[CustomerActionDate], DateTable[Date] ) ) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡