Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

1 REPLY 1
Greg_Deckler
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
RETURN
  SWITCH(__Type,
    "Selected",SUMX(FILTER('Table',[Merchant] = __Merchant),[Value]),
    SUMX(FILTER('Table',[Merchant] <> __Merchant),[Value])
  )


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.