Forum Discussion

gvg's avatar
gvg
Post Prodigy
5 years ago
Solved

Using table as a filter to CALCULATE

Hi,

I have 2 simple tables that are related through column Manager:

I am trying to use testFilter table as a filter in CALCULATE. When I test the filters in DAX Studio with EVALUATE testFilter and EVALUATE Values(testFilter[Manager]), they both produce same result - a table of one column Manager. Can anybody tell me why the following two measures give different results?

 

SumFiltered =            CALCULATE( SUM( Sales2[Amount]), testFilter )
SumFilteredValues = CALCULATE( SUM( Sales2[Amount]), VALUES(testFilter[Manager]) )
 
The results I receive are as follows:
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI gvg,

    In fact, raw table visual not suitable to directly use as filters.
    For your requirement, you need to select one row, then power bi can pass the selected value as a filter to interact with other visuals if you turn on the interaction between these visuals. Then the DAX expression can extract the selections and calculate the corresponding results. (these effects will be lost if you cancel the selections on the current table)

    Change how visuals interact in a Power BI report 

    Overwrite Interaction of Power BI with DAX 

    Regards,

    Xiaoxin Sheng

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    gvg , What I got it, the first one is a filter. And the second one is to group the data first at testFilter[Manager] level

     

     

    what is grouping column here ?

    • gvg's avatar
      gvg
      Post Prodigy
      5 years ago

      amitchandak , the grouping column is Manager. Why do you think VALUES (testFilter[Manager] ) represent some sort of grouping ? It is a table as any other table. As filters in CALCULATE are said to be tables, I thought that these two measures should yield same result. 
      After I tested these scenarios more thoroughly, I see that VALUES (testFilter[Manager] ) in fact returns 3 rows, not 2 rows in table testFilter. The third row is blank row suggesting that VALUES works on expanded tables. Still, however, I am lost why would VALUES (testFilter[Manager] ) not filter as expected (i.e. Peter and Tom).

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI gvg,

    In fact, raw table visual not suitable to directly use as filters.
    For your requirement, you need to select one row, then power bi can pass the selected value as a filter to interact with other visuals if you turn on the interaction between these visuals. Then the DAX expression can extract the selections and calculate the corresponding results. (these effects will be lost if you cancel the selections on the current table)

    Change how visuals interact in a Power BI report 

    Overwrite Interaction of Power BI with DAX 

    Regards,

    Xiaoxin Sheng