Dynamically Change Calculation Based on Slicer

[PBIUser324]

I have 2 slicers that I want the user to be able to select and change a calculated measure. I want it to function like the following, however this will not work because FILTER outputs a table and SWITCH must output a scalar. I have tried the same without using FILTER, however for the same reason, it does not work. 

Product Penetration =

VAR PeriodSelection = [Selected Period]  -- YTD or Q1 2025

VAR ProductSelection = [Selected Product]  -- Product

VAR DateFilter =

    SWITCH(

        PeriodSelection,

        "YTD", DATESYTD('Data Model'[Last Logged in Date]),

        "Q1 2025", FILTER('Data Model', 'Data Model'[Quarter Year] = "Q1 2025")

    )

VAR ProductFilter =

SWITCH(

    ProductSelection,

    "Quick Benchmarks", FILTER('Data Model','Data Model'[Quick Benchmarks] = "Y"),

    "Market Queries", FILTER('Data Model','Data Model'[Market Queries] = "Y"),

    "Talent Intelligence", FILTER('Data Model','Data Model'[Quick Benchmarks] = "Y"), --ATTENTION

    "Location Analytics", FILTER('Data Model','Data Model'[Location Analytics] = "Y"),

    "Country Totals", FILTER('Data Model','Data Model'[Country Totals] = "Y"),

    "Employee vs Market", FILTER('Data Model','Data Model'[Employee vs. Market] = "Y"),

    "Participation Report", FILTER('Data Model','Data Model'[Quick Benchmarks] = "Y"), -- ATTENTION

    "Organizational Design", FILTER('Data Model','Data Model'[Organisation Design] = "Y"),

    "Talent Mobility", FILTER('Data Model','Data Model'[Talent Mobility] = "Y"),

    "Diversity Representation", FILTER('Data Model','Data Model'[Diversity Representation] = "Y"),

    "Executive Regression", FILTER('Data Model','Data Model'[Executive Regression] = "Y"),

    "Data Extractor ", FILTER('Data Model','Data Model'[Data Extractor (McLagan only)] = "Y"),

    "Job Offers", FILTER('Data Model','Data Model'[Job Offer] = "Y"),

    "ERROR"

)

VAR BaseCalculation = CALCULATE(DISTINCTCOUNT('Data Model'[Client Child Code]), 'Data Model'[Is Active User] = "Y", ProductFilter, DateFilter ) / CALCULATE(DISTINCTCOUNT('Data Model'[Client Child Code]), 'Data Model'[Is Active User] = "Y", DateFilter)

RETURN

BaseCalculation

[Deku · Accepted solution]

You have 3 options.

Calculate the base measure within each option of the switch statement

Define each productselection filter logic in a calculation group which you can apply to the base measure

Or create measures for each option and use field parameters

[v-ssriganesh]

Hi @PBIUser324,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @PBIUser324,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @PBIUser324,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @grazitti_sapna  & @Deku for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[grazitti_sapna · Accepted solution]

Hi @PBIUser324 , 

You can try using: 

Product Penetration =
VAR PeriodSelection = [Selected Period] -- YTD or Q1 2025
VAR ProductSelection = [Selected Product] -- Product

VAR DateFilter = 
SWITCH(
TRUE(),
PeriodSelection = "YTD", DATESYTD('Data Model'[Last Logged in Date]),
PeriodSelection = "Q1 2025", 
KEEPFILTERS('Data Model'[Quarter Year] = "Q1 2025")
)

VAR ProductFilter =
SWITCH(
TRUE(),
ProductSelection = "Quick Benchmarks", 'Data Model'[Quick Benchmarks] = "Y",
ProductSelection = "Market Queries", 'Data Model'[Market Queries] = "Y",
ProductSelection = "Talent Intelligence", 'Data Model'[Quick Benchmarks] = "Y", 
ProductSelection = "Location Analytics", 'Data Model'[Location Analytics] = "Y",
ProductSelection = "Country Totals", 'Data Model'[Country Totals] = "Y",
ProductSelection = "Employee vs Market", 'Data Model'[Employee vs. Market] = "Y",
ProductSelection = "Participation Report", 'Data Model'[Quick Benchmarks] = "Y", 
ProductSelection = "Organizational Design", 'Data Model'[Organisation Design] = "Y",
ProductSelection = "Talent Mobility", 'Data Model'[Talent Mobility] = "Y",
ProductSelection = "Diversity Representation", 'Data Model'[Diversity Representation] = "Y",
ProductSelection = "Executive Regression", 'Data Model'[Executive Regression] = "Y",
ProductSelection = "Data Extractor ", 'Data Model'[Data Extractor (McLagan only)] = "Y",
ProductSelection = "Job Offers", 'Data Model'[Job Offer] = "Y",
FALSE
)

VAR BaseCalculation =
CALCULATE(
DIVIDE(
DISTINCTCOUNT('Data Model'[Client Child Code]),
CALCULATE(DISTINCTCOUNT('Data Model'[Client Child Code]))
),
'Data Model'[Is Active User] = "Y",
DateFilter,
ProductFilter
)

RETURN
BaseCalculation

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Deku · Accepted solution]

You have 3 options.

Calculate the base measure within each option of the switch statement

Define each productselection filter logic in a calculation group which you can apply to the base measure

Or create measures for each option and use field parameters