Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent 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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors