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!