Forum Discussion

PBIUser324's avatar
PBIUser324
New Member
1 year ago
Solved

Dynamically Change Calculation Based on Slicer

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 SWI...
  • Deku's avatar
    1 year ago

    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

  • grazitti_sapna's avatar
    1 year ago

    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!