Multiple filters using two tables DAX

[Anonymous]

I want to apply filters from two tables to count a numbe of items using DAX. Is this possible? In Excel would use vlookup and pivot.

From the two tables, I want to see how many items in the Vegetables category are sold.

Table 1:

     Item             Category

Apples	Fruit
Bananas	Fruit
Belt	Accessory
Betroot	Vegetable
Candles	Kitchenware
Carrots	Vegetable
Chair	Lounge
Eraser	School Material
Foil Wraps	Kitchenware
Ice Blocks	Misc
Microwaves	Kitchenware
Oranges	Fruit
Peaches	Fruit
Pencils	School Material
Phones	Accessory
Shirt	Clothing
Shoes	Clothing
Spinach	Vegetable
Spoon	Kitchenware
Table	Lounge
Watch	Accessory

Table2:

     Items         Status

Apples	Sold
Bananas	Instock
Belt	Instock
Betroot	Sold
Candles	Instock
Carrots	Instock
Chair	Instock
Eraser	Sold
Foil Wraps	Instock
Ice Blocks	Sold
Microwaves	Sold
Oranges	Instock
Peaches	Sold
Pencils	Sold
Phones	Instock
Shirt	Instock
Shoes	Sold
Spinach	Sold
Spoon	Sold
Table	Instock
Watch	Sold

[amitchandak]

In case the item table is having unique items. Join both tables on items.

Use a formula like

calculate(count(table2[item]),table2[sold]="sold",table1[Category]="Vegetable")

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin