Only calculate measure where rows meet specific condition in another column

[JadeM]

Hi all,

I have the below measure which I am using to calculate whether staff movement is 'In' or 'Out'. It should show 'In' where there are two instatnces of one name in the filter view and 'Out' if a name only appears once. This measure needs to be dynamic so that the value of 'In' or 'Out' changes depending on the filters applied.

The below works correctly.

New Column = 
  var thisName = SELECTEDVALUE(Table1[Name])
  var nameCount = COUNTROWS(FILTER(ALLSELECTED(Table1), [Name] = thisName))
  var result = IF(nameCount > 1, "In", "Out")
  return IF(ISINSCOPE('Table1'[Name]), result)

I would now like to edit this measure so that the calculation only spits out 'In' or 'Out' where the value in the column 'Department' is 'Sales' or 'Marketing'. All otehr departments should be blank.

Here is an example of my data:

Table1 (no filters) John shows as 'In' in the new column as his name appears twice.

Name Department New Column

John	Sales	In
John	Marketing	In
Julie	Marketing	Out
Jake	Sales	Out
Simon	IT

If you then filter Department to show just Marketing (using a slicer on the same page), I would like John to change to 'Out' as there is now only one occurrence of the name John.

Name Department New Column

John	Marketing	Out
Julie	Marketing	Out

I do not want there to be a value next to Simon in IT as he is not in Marketing or Sales - how can I update the measure to incorporate this second requirement?

Thanks in advance.

[Anonymous · Accepted solution]

Hi @JadeM ,

1.The measure can be modified to:

New Column =

  var thisName = SELECTEDVALUE('Table'[Name])

  var nameCount = COUNTROWS(FILTER(ALLSELECTED('Table'), [Name] = thisName))

  var result =

  IF(SELECTEDVALUE('Table'[Department]) = "Marketing" || SELECTEDVALUE('Table'[Department]) = "Sales",

  IF(nameCount > 1, "In", "Out"),BLANK())

  return IF(ISINSCOPE('Table'[Name]), result)

2.Set the Name or Department field to show items with no data so that when there is no data in the New Column column, the other columns can still be displayed normally.

The results obtained are shown below:

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@JadeM Maybe:

New Column = 
  var thisName = SELECTEDVALUE(Table1[Name])
  var thisDept = SELECTEDVALUE(Table1[Department])
  var nameCount = COUNTROWS(FILTER(ALLSELECTED(Table1), [Name] = thisName))
  var result = SWITCH(TRUE(), thisDept = BLANK(), BLANK(), nameCount > 1, "In", "Out")
  return IF(ISINSCOPE('Table1'[Name]), result)