topic Re: Filter context in DAX Commands and Tips

Filter context

Anonymous — Wed, 04 Nov 2020 06:11:16 GMT

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?

Re: Filter context

AntrikshSharma — Tue, 03 Nov 2020 18:23:26 GMT

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" )

Re: Filter context

CNENFRNL — Tue, 03 Nov 2020 18:36:12 GMT

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!

Re: Filter context

Anonymous — Wed, 04 Nov 2020 06:15:59 GMT

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 😞

Re: Filter context

Anonymous — Wed, 04 Nov 2020 06:59:24 GMT

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:

Re: Filter context

Anonymous — Wed, 04 Nov 2020 07:12:07 GMT

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 ?

Re: Filter context

AntrikshSharma — Wed, 04 Nov 2020 09:44:33 GMT

Anonymous Can you please attach sample dataset?