Forum Discussion
Dynamically Change Calculation Based on Slicer
- 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
- 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!
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!