Filter context

[Anonymous]

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?

[AntrikshSharma]

@Anonymous  Can you please attach sample dataset?

[CNENFRNL]

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]

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]

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

[AntrikshSharma]

@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]

@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 😞