Question

Implementing a Slicer to Toggle Between Daily, Monthly, and Yearly Sales Aggregation
Your Power BI report contains a line chart showing sales over time, and the client wants an option to toggle between "Daily," "Monthly," and "Yearly" aggregation using a slicer. What is the best way to implement this using DAX and Power BI features to ensure smooth user interaction?

Answer 1

1. Create an Aggregation Table with values: Daily, Monthly, Yearly.

2. Create a DAX Measure using SWITCH to adjust aggregation:

Sales Aggregation = 
SWITCH(
    SELECTEDVALUE(AggregationTable[Aggregation], "Daily"),
    "Daily", SUM(Sales[Amount]),
    "Monthly", CALCULATE(SUM(Sales[Amount]), VALUES('Date'[YearMonth])),
    "Yearly", CALCULATE(SUM(Sales[Amount]), VALUES('Date'[Year])),
    SUM(Sales[Amount])
)

3. Add a Slicer using AggregationTable[Aggregation] and use Sales Aggregation in a line chart.