Forum Discussion
UNDERSTANDING CALCULATE FUNCTION
Hi Team,
I have the dataset as below
Product Table
| Color | FruitName |
| Green | apples |
| Yellow | bananas |
| Orange | oranges |
Sales table
FruitName OrderDate Week Gender Quantity SaleAmt
| apples | 05-01-2017 | 2 | Male | 101 | 0.14 |
| apples | 05-01-2017 | 2 | Female | 201 | 0.14 |
| apples | 06-01-2017 | 2 | Female | 352 | 0.28 |
| bananas | 06-01-2017 | 2 | Male | 632 | 0.28 |
| bananas | 06-01-2017 | 2 | Male | 412 | 0.28 |
| apples | 07-01-2017 | 2 | Male | 258 | 0.21 |
| bananas | 07-01-2017 | 2 | Female | 658 | 0.28 |
| oranges | 07-01-2017 | 2 | Female | 962 | 0.21 |
I have a measure
Only Yellow Color Sales:=calculate(sumx(SalesB,SalesB[Quantity]*SalesB[SaleAmt]),ProductsB[Colour]="Yellow")
My question is why in the table am i not able to find the Fruite name for Color Green & Orange? However when i try to put another measure im getting the Fruitname
Yellow Color & Banana Fruit :=CALCULATE(sumx(SalesB,SalesB[Quantity]*SalesB[SaleAmt]),ProductsB[Colour]="Yellow",ProductsB[FruitName]="Bananas")
Can someone explain me this please?
Hi Anonymous ,
When the second argument of
CALCULATEis a boolean filter rather than a table of values, it is equivalent toFILTER(ALL(column), filter): that is:CALCULATE(sumx('Sales table','Sales table'[Quantity]*'Sales table'[SaleAmt]),FILTER(ALL('Product Table'[Color]),'Product Table'[Color]="Yellow")).AndCALCULATEdoes indeed override the filter context when evaluating its first argument; using the rows provided to its second argument as the new filter context. However;CALCULATEdoes not alter the filter context when evaluating its second argument, and so pre-existing filters remain unless they are explicitly removed.refer:https://stackoverflow.com/questions/49909388/dax-calculate-doesnt-seem-to-be-overriding-pivot-filterBest RegardsLucien
5 Replies
Anonymous , Please refer to this for the difference between calculate with and without filter expression
http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/
amitchandak i understand that having FILTER() inside calculate will get the table of only specified data ..However in my measures i havent used FILTER() ....I want to know why the fruit name is reflecting in one measure and not in another one?
Anonymous when you are giving DAX condition like this ProductsB[Colour]="Yellow", it removes the filter from the Color column and that's why you see repeated value for all the color. IN this case, it removes the filter on color column, and in 2nd example, it removes the row filter on both color and fruit, and that's why you see the fruit in all the lines.
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi Anonymous ,
When the second argument of
CALCULATEis a boolean filter rather than a table of values, it is equivalent toFILTER(ALL(column), filter): that is:CALCULATE(sumx('Sales table','Sales table'[Quantity]*'Sales table'[SaleAmt]),FILTER(ALL('Product Table'[Color]),'Product Table'[Color]="Yellow")).AndCALCULATEdoes indeed override the filter context when evaluating its first argument; using the rows provided to its second argument as the new filter context. However;CALCULATEdoes not alter the filter context when evaluating its second argument, and so pre-existing filters remain unless they are explicitly removed.refer:https://stackoverflow.com/questions/49909388/dax-calculate-doesnt-seem-to-be-overriding-pivot-filterBest RegardsLucienHi Anonymous ,
Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.
Best Regards
Lucien
