Use values from external tables for filtering table dynamically

[Anonymous]

I have several tables that filter a fact table (not linked by any variable in the data model).
The columns which contain the values are chosen dynamically by a measure. 

Minimal example with three tables:

Table FOOD

F_1	F_2	F_3
Food	Fruits	Apples
Food	Fruits	Oranges
Food	Meat	Chicken

Table COUNTRY

C_1	C_2	C_3	C_4
WORLD	EUROPE	FRANCE	PARIS
WORLD	EUROPE	ITALY	ROME
WORLD	EUROPE	ITALY	VENICE
WORLD	AMERICA	MEXICO	MEXICO

Table FACT

FOODSTUFF	GEOGRAPHY	VALUE
Food	PARIS	1
Food	ROME	2
Food	VENICE	3
Food	MEXICO	4
Food	FRANCE	5
Food	ITALY	6
Food	MEXICO	7
Food	EUROPE	8
Food	AMERICA	9
Food	WORLD	10
Fruits	MEXICO	11
Fruits	FRANCE	12
Fruits	ITALY	13
Fruits	MEXICO	14
Fruits	EUROPE	15
Fruits	AMERICA	16
Fruits	WORLD	17
Apples	MEXICO	18
Oranges	EUROPE	19
Chicken	AMERICA	20
Meat	WORLD	21

Measure FCol tells me at what F_ should I look and CCol at what C_ .
My aim would be to

CALCULATE(SUM(FACT[VALUE]),FILTER(FACT,FACT[FOODSTAFF] in VALUES( Something here to obtain F_ based in FCol),FILTER(FACT,FACT[GEOGRAPHY] in VALUES( Something here to obtain C_ based in CCol)) 

I tried to include the values in a VAR but that didn't work.

How can I combine the filters together?

[v-yulgu-msft]

Hi @Anonymous ,

I'm not very clear about what you were trying to achieve based on current description. Please show us the desired output with examples. What visual did you use? How to apply drill down? How to apply filters and which column should be added to slicer?

Regards,

Yuliana Gu

[MFelix]

Hi @Anonymous,

What is the purpose of having in the fact table a mix between the columns of the Fruit and of the Country?

Having the filter tables setup with and hierarchy model and then having any of the columns on your fact table will not get you the correct results since you won't be abble to pinpoint wich table you are refering to.

What is the purpose of your selection is to have the selection of Apples and then get only apples or do you also want to get the higher level in the table FOOD so Fruits and Food?

Or do you want to select FOOD or FRUITS or APPLES and get only the values for that specific selection?

Regards,

MFelix

[Anonymous]

Hello MFelix,

In the real case I have a third hierarchy that I use for drilling up and down. I embed the hierarchy logic of the visuals using in scope and in order to define the hierarchycal filters the easiest way to go I thought of is allowing a moving filter dependant on the maximal hierarchy level that can be displayed (Those higher levels usually have more inforamtion available in other dimensions).

To your question I'll never sum two different levels in a chart (I either sum apples and oranges or fruits). However, all combinations of (F_x , C_x) are possible.

Kind regards,

Jon