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 statistics'[User Principal Name]
VAR amount_per_user= CALCULATE(SUM('Teams statistics'[Team Chat Message Count]), FILTER('Teams statistics','Teams statistics'[User Principal Name]=current_user))
return
IF(amount_per_user>=2, "Yes", "No")
 
And as per screenshot where count is 1 it says "Yes" but as per above condition it should not. Can you advise how to amend it?

 

Need Help

6 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    5 years ago

    Anonymous  Is 'Teams statistics'[User Principal Name] a measure or a refrence to a column or you didn't wrap inside SELECTEDVALUE?

    Try this:

     

    Teams Channels =
VAR current_user = 'Teams statistics'[User Principal Name]
VAR amount_per_user =
    CALCULATE (
        SUM ( 'Teams statistics'[Team Chat Message Count] ),
        'Teams statistics'[User Principal Name] = current_user,
        REMOVEFILTERS ( 'Teams statistics' )
    )
RETURN
    IF ( amount_per_user >= 2, "Yes", "No" )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      AntrikshSharma 

      Thank you and apologies, this is not the measure but calculated column I am trying to get as eventually I will have to count "Yes'es" and not sure if masure will serve this purpose...?

       'Teams statistics'[User Principal Name]  is a column, and I di not wrap it in VALUES. 

      I am afraid your suggestion still gives me same result 😞

  • 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 ?