Retrieve Common Records

[nihhalmca]

Hi All,

My Data Model has 2 fact tables. Common column should be 'ID'.

Fact 1          Fact 2

ID                ID

1                  1

2                  2

3                 

4

Note: I want to display 'ID' in chart expression where ID has in both the fact tables. Moreover i do not want to disturb data model for example Concatenate, Link table etc.

Thanks,

Nihhal.

[Anonymous]

Well,

In this case you can create a different field name with same values, say

ID as TestID then

you can compare like:

IF(ID=TestID............sth like this..

[sunny_talwar]

You want to see this where? List Box? Table Box? Straight Table?

[nihhalmca]

Pivot table.

[sunny_talwar]

Try with a calculated dimension: (and Select 'Suppress When Value Is Null' option on dimension tab)

=If(Len(Trim(FieldFact1)) > 0 and Len(Trim(FieldFact2)) > 0, ID)

Where FieldFact1 is a field exclusively from Fact 1 table and FieldFact2 is exclusively from Fact 2 table.

[maxgro]

if Exp5 is a field in Fact 2, you can try with a calculated dimension setting null the dimension when you don't have Exp5 (else the dimension is ID)

=if(aggr(count(Exp5),ID)=0,null(), ID)

[nihhalmca]

Thanks for the response Sunny.

Just i used only ID there is no measure.

Ex: Count({<Theater = {'NA'}, (Where matched records in ID from the both tables) >} ID)

Nihhal.

[sunny_talwar]

Not a problem at all.

I am glad I was able to help.

Best,

Sunny