Single Date Slicer for multiple date columns with relationships between tables

[Anonymous]

Hi folks,

I have 3 tables:

- Accounts, with account_id and account open year

- Requests, with requests open year (and a relationship of 1-many between account and requests)

- Tasks, with tasks open year (and a relationship of 1-many between requets and tasks)

All relationships are active, and now the user requested to have a year filter where he needs to know what is the number of transacitons happened in a certain year for the 3 mentioned tables (count of accounts opened, requests and tasks).

I created a global dim table called year, added a year value into slicer. Defintly you can't relate this global table with the 3 tables since there are relationships already between them. 

How can I have a single year value that filteres all data coming from the 3 different sources? I uploaded my PBIX file here.

[negi007 · Accepted solution]

@Anonymous in this case, you will have to create seperate measures to calculate your values and using inactive relationship.

Account_Open# = CALCULATE(COUNT(Accounts[Account_id]),USERELATIONSHIP('year'[Year],Accounts[Open_Year]))

Account_Requst# = CALCULATE(COUNT(Requests[Account_Id]),USERELATIONSHIP('year'[Year],Requests[Request_year]))

TAsk# = CALCULATE(COUNT(Tasks[Task_Id]),USERELATIONSHIP('year'[Year],Tasks[Task_year]))

i am attaching pbix file that you can modify as per your needs. but this should help you and take you in the right direction.

[Anonymous]

Yup :), I used to have it before but resulted in a low performance issue given the fact of too much measures.

Isn't there any alternative solution?