Calculate on two fact tables linked to same dimension tables

[QQ]

Your help highly appreciated!

I have two fact tables: fct_Case and fct_Client

fct_Case

CaseID

ClientID

Date

Group

fct_Client

ClientID

QuarterDate

Active

Group

And 3 dimension tables which the fact tables are linked to.

dim_Date

Date

Quarter

 linked to the fact tables by [Date] column

dim_Group

Group

linked to the fact tables by [Group] column

dim_Client ID

Client ID

linked to the fact  tables by [Client ID] column

I need to calculate on the numbers of cases where the clients are active in one specific quarter. Here is the measure I generate:

= calculate(distintcount(fct_Case[CaseID]), filter(fct_Client, fct_Client[Active]="Y"), DatesBetween(fct_Case[Date],Date1, Date2))

This measure works if dim_Group and dim_Date are removed. However it gives wrong value with these two dimension tables included. 

I understand this is due to the multple possible links between the two fact tables. I tried to add Userelationship into the measure but it does not work either. 

Any possible solution to generate a measure to solve the issue? Thanks a lot!

[bdymit]

You are trying to have a filter that you are putting on one fact table affect your other fact table.

The problem is that fact tables can only be affected by filters on themselves or by filters on dimensional tables related to them.

In other words, your relationships between tables are "one to many" relationships (unless you have a more complicated data model with bi-directional filtering, in which case you should make that known). A filter on a dimensional table (the "one" side of the relationship) filters your fact table as well (the fact table is the "many" side of the relationship.)

A filter on your fact table will not affect your dimensional table.

I am experimenting with a some ideas, but I am wondering, what is the relationship between fct_Client and dim_Date?

If fct_Client[QuarterDate] related to dim_Date[Date]? Or do you have it somehow related to dim_Date[Quarter]?

[bdymit]

Also, is Group an identifier for ClientID?

Such that each ClientID belongs to one group?

Thanks!

[QQ]

Group and ClientID are many to many relationship as well, one group have multiple ClientIDs and one ClientID can have multiple groups.

The fct_Client table records are at quarterly Client level. It looks like:

ClientID	QuarterDate	Active	Group
1	01-01-2010	Yes	A
1	01-04-2010	Yes	A
1	01-07-2010	No	A
1	01-10-2010	Yes	B
1	01-01-2011	Yes	B
1	01-04-2011	Yes	B
2	01-01-2010	No	C
2	01-04-2010	No	C
2	01-07-2010	Yes	A
2	01-10-2010	Yes	A
2	01-01-2011	Yes	A
2	01-04-2011	Yes	A

The [QuarterDate] is linked to [Date] in the dim_Date table. 

Thanks!

[v-yulgu-msft]

Hi @QQ,

Could you try to replace fct_Case[Date] with dim_Date[Date] in measure formula?

Measure= calculate(distintcount(fct_Case[CaseID]), filter(fct_Client, fct_Client[Active]="Y"), DatesBetween(dim_Date[Date],Date1, Date2))

Also, what is the detailed data in fct_Case table?

Regards,
Yuliana Gu

[QQ]

Just tried and unfortunately not working. But thanks a lot Yuliana!

I think I may have to change the data model and not filter the fact table but put [Active] into one of the dimension table so I can filter on the dimension table.

Thank you all for your help!

Qing

[v-yulgu-msft]

Hi @QQ,

Thanks for your sharing. 

Regards,
Yuliana Gu