Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

UNDERSTANDING CALCULATE FUNCTION

Hi Team,   I have the dataset as below                                Product Table Color FruitName  Green apples Yellow bananas Orange oranges Sales table FruitName        ...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi Anonymous ,

    When the second argument of CALCULATE is a boolean filter rather than a table of values, it is equivalent to FILTER(ALL(column), filter): that is: 

    CALCULATE(sumx('Sales table','Sales table'[Quantity]*'Sales table'[SaleAmt]),

    FILTER(ALL('Product Table'[Color]),'Product Table'[Color]="Yellow")).

     

     

     

    And CALCULATE does indeed override the filter context when evaluating its first argument; using the rows provided to its second argument as the new filter context. However; CALCULATE does not alter the filter context when evaluating its second argument, and so pre-existing filters remain unless they are explicitly removed.
     
     
     
     
    Best Regards
    Lucien
parry2k's avatar
parry2k
Super User
5 years ago

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.⚡