Forum Discussion

AndreaLorenzo's avatar
AndreaLorenzo
Frequent Visitor
5 years ago

Calculate not working when using same field in table and formula filter

Hello everybody,   I'm trying to do something very simple, but I can't make it work:  from a Sales table, I need to compare the revenue for every product category related to another one.   I've  ...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

AndreaLorenzo You are running into yet another reason I don't use CALCULATE. Basically, if you use CALCULATE with a straight filter clause (no FILTER function) then if the column you specify is already in the filter context, it replaces the filter context for that column. Otherwise, it adds to the filter context. 

CALCULATE function (DAX) - DAX | Microsoft Docs

 

It's hard to tell because I don't know your source data but it is probably something along those lines. As a general rule, if a function needs 50 blog articles to explain how it works, I tend to avoid it.

  • AndreaLorenzo's avatar
    AndreaLorenzo
    Frequent Visitor
    5 years ago

    Thanks for the feedback, Greg_Deckler . Calculate is a bit difficult to understand, yes.

     

    I finally got it working, maybe is not the most elegant solution, but it will do... 

     

    H_Sales= var _tbl = ALLSELECTED(PBI_Sales)

    var _catFiler= "H"

    RETURN SUMX(FILTER(_tbl;  PBI_Sales[CategoryID]=_catFilter); [Revenue])
    Thanks!
    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

      AndreaLorenzo That's pretty much exactly how I would have done it.