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