Forum Discussion
Solved
Reference current row value in Filter expression
Hi ,
I seem to be struggling with a problem in PowerBI.
I will try to explain the issue with the Product and Sales table example .
In my report , I have a drop down filter for Product and I have a Sales table from which I want to display the sales for the Product and also the total sales for the Product Category under which the Item belongs
PRODUCT TABLE
ProductCategory Item
| Book | B1 |
| Book | B2 |
| Clothing | Dress |
| Furniture | Table |
| Book | B3 |
| Clothing | Shirt |
| Furniture | Chairs |
SALES TABLE
Item Sales ProdCat
| B1 | 100 | Book |
| Dress | 20 | Clothing |
| B2 | 200 | Book |
| Table | 10 | Furniture |
| Shirt | 30 | Clothing |
| B3 | 20 | Book |
| Chairs | 60 | Furniture |
When I hardcode a particular Product Category , the DAX works :
ProductSales = CALCULATE(sum(Sales[Sales]),FILTER(ALL(Sales), Sales[ProdCat] = "Book"))
In the Above DAX , I want to dynamically reference the Product Category of the item selected in the drop down list box .
I tried using EARLIER function to reference the current value of Sales[ProdCat] but I got the below error .
How do I override the Filter Context and also apply filter based on the current value of the row?
Any advice would be greatly appreciated.
=VAR _prdcat=VALUES(Product[ProductCategory]) RETURN CALCULATE(SUM(Sales[Sales]),FILTER(ALL(Product,Product[ProductCategory] IN _prdcat))
2 Replies
=VAR _prdcat=VALUES(Product[ProductCategory]) RETURN CALCULATE(SUM(Sales[Sales]),FILTER(ALL(Product,Product[ProductCategory] IN _prdcat))
Thank you so very much !!!! That worked .
Very grateful for your help .
