Creating a calculated measure using criteria from another table

[AndrewMcCorkell]

Hi all

I have a table where redemption data for multiple gift card merchants is stored, along with the redemption values, dates etc. I'm trying to create a visual where I can select one or more of the merchants and NOT filter the table, but instead I want to spotlight those selected merchants and their redemption value totals, and have all the others grouped together to appear in the visual under 'Other'. The idea is that I want to be able to share this with the merchants directly, so I need to disguise data from any other merchant and put it all under the 'other' umbrella.

My approach so far has been to create a separate 'master' table of merchants, to which I apply a slicer and nominate the selected merchants here, as the ones I want to spotlight in the visual. I'm thinking one calculated measure will use the criteria from this 'master merchants' list to provide redemption value for the selected merchants, while another calculated measure will provide a total of redemptions from 'other' merchants. I've edited the interactions to prevent the slicer from filtering the main table, but I can't figure out how to use these selected merchants from the 2nd table as the criteria with which to spotlight the selected merchants in the first table, without filtering the first table.

This would be much easier explained face-to-face! Fingers crossed some of you know what I'm trying to do here....

[Greg_Deckler]

@AndrewMcCorkell So, I would do this with the disconnected master table you describe but create another disconnected table with just the values "Selected" and "Other". Use this as the axis in a visual. You can then get the SELECTEDVALUE from your slicer table in a measure like this:

Measure = 
  VAR __Merchant = SELECTEDVALUE('SlicerTable'[Merchant])
  VAR __Type = MAX('ParameterTable'[Value]) // this is either Selected or Other
RETURN
  SWITCH(__Type,
    "Selected",SUMX(FILTER('Table',[Merchant] = __Merchant),[Value]),
    SUMX(FILTER('Table',[Merchant] <> __Merchant),[Value])
  )