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 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
  • 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

  • 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!

5 Replies

  • Deku's avatar
    Deku
    Super User

    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

  • 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!

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support

    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.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support

    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's avatar
    v-ssriganesh
    Community Support

    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.