Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Filter context

Hi all. I can not seem to figure out the filter context and calculated column will not give me the expected results. The measure is the following: Teams Channels = VAR current_user = 'Teams stati...
Need Help
CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

Hi, Anonymous , with regard to this part of the measure in question, 

VAR amount_per_user =
CALCULATE (
    SUM ( 'Teams statistics'[Team Chat Message Count] ),
    FILTER (
        'Teams statistics',
        'Teams statistics'[User Principal Name] = current_user
    )
)

 'Teams statistics' is already filtered in the initial filter context (row from table/matrix, selection of slicer, interaction from other viz, etc.) before it's further filtered by FILTER().

 

I assume that you tend to filter the whole 'Teams statistics' table with condition 'Teams statistics'[User Principal Name] = current_user; if so, this variable can be simplified this way,

VAR amount_per_user =
CALCULATE (
    SUM ( 'Teams statistics'[Team Chat Message Count] ),
    'Teams statistics'[User Principal Name] = current_user
)

This classic article by Jeffrey Wang unveiled some sophisticated details of mechanism of filter context. I strongly recommend it if you'd like to dig deeper into filter context in DAX. Enjoy!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks, CNENFRNL 

    Your example is bit closer to what I want but still not ideal.

     

     

    Now one user gives me wrong result and somehow I think I need to take period into the account? The example shows where 2 is shown as "Yes" which is great and bottom one is wrong. I am also filtering the visual itself by week offset (taking only last 4 weeks) . The difference between these users is:

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      CNENFRNL , also Teams Channels is calculated column I am trying to create as later I will have to count "Yes'es". Could it be the problem ?