Forum Discussion
Count Only Correctly Associated Keys
I have a table where I am trying to only count the rows that are correctly associated. I have two dimension tables, that link to my fact table.
Key Table
| Name | Key |
| Fruit | 1 |
| Vegetable | 2 |
Subcategory Key
| Food Type | Food Subcategory Type | Food Subcategory Key | Food Type Key |
| Fruit | Apple | 1 | 1 |
| Vegetable | Brocolli | 2 | 2 |
| Fruit | Banana | 3 | 1 |
| Vegetable | Carrot | 4 | 2 |
| Customer # | Food Subcategory | Food Category | Was Eaten? | Food Category Key | Food Subcategory Key |
| 11111 | Apple | Fruit | Y | 1 | 1 |
| 11111 | Brocolli | Fruit | Y | 1 | 2 |
| 11111 | Brocolli | Vegetable | Y | 2 | 2 |
Is there a way to construct a DAX measure where each of the dim tables is connected to the fact table (and each other), that would only count when Brocolli is listed as a vegetable and not a fruit? The data I'm working with has multiple subcategory types, so would be curious if the measure would only count the row if the keys were correct and lined up in the dimension table.
2 Replies
hi Anonymous
Try to get the [Food Category Key] and [Food Subcategory Key] columns in your fact table by merging with the Subcategoty dimtable based on two columns: [Food Category] and [Food SubCategory].
FreemanZ I am not sure I am following, the two key tables are linked and are also linked to the master fact table
