Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Creating a calculated measure using criteria from another table

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....

Super User
Super User

@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
    "Selected",SUMX(FILTER('Table',[Merchant] = __Merchant),[Value]),
    SUMX(FILTER('Table',[Merchant] <> __Merchant),[Value])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors