Slicers based on a fact table A can filter data of another fact table B ?

[Orion]

Hye,

I am wondering about one question concerning the filters and the slicer.

I have 2 fact tables "Fruit" and "Supplier". The slicer is based on the fact table "Fruit" and I want that the selections (multiple or single) done in the slicer must impact also the fact table "Supplier".

I think it is possible only with the DAX function but I do not know how to do ? Can you help me?

Thank you.

There is a sample :

[Vvelarde · Accepted solution]

@Orion

Try creating a new table with year and Month columns and related to both fact tables.

[Greg_Deckler]

So, if there is no relationship between your two tables, how do you propose having the filter affect an unrelated table? Are you saying that if you select a Fruit for year 2001, month 2 that you only want to show Suppliers from that year and month? If that is the case, combine your Year and Month fields in both tables into a new field and then create a relationship between them. I'll test to see if it works if that is what you are wanting to do.

[Orion]

thank you for your answer.

So, if there is no relationship between your two tables, how do you propose having the filter affect an unrelated table? Are you saying that if you select a Fruit for year 2001, month 2 that you only want to show Suppliers from that year and month?

YES

If that is the case, combine your Year and Month fields in both tables into a new field and then create a relationship between them. I'll test to see if it works if that is what you are wanting to do.

my case is more complicated I tried ti simplify in my sample.

I cannot created relation between the two fact tables because there are n:n cardinality.

[Orion]

So, if there is no relationship between your two tables, how do you propose having the filter affect an unrelated table? Are you saying that if you select a Fruit for year 2001, month 2 that you only want to show Suppliers from that year and month?

YES

If that is the case, combine your Year and Month fields in both tables into a new field and then create a relationship between them. I'll test to see if it works if that is what you are wanting to do.

[v-shex-msft · Accepted solution]

Hi @Orion,

As Vvelarde's point of view, create a calendar table based on these tables' date filed,for example:

DateTable = CALENDAR(MIN(VALUES(Table1[Date]),VALUES(Table2[Date])),MAX(VALUES(Table1[Date]),VALUES(Table2[Date])))

Then create the relationship between datetable and above tables, use the date field which in the calendar table as the source of slicer.

Regards,
Xiaoxin Sheng

[Orion]

Thank you for your response

[Vvelarde · Accepted solution]

@Orion

Try creating a new table with year and Month columns and related to both fact tables.