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 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 |
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
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 |
---|---|
104 | |
96 | |
79 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |