Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |