Forum Discussion

Lucy01's avatar
Lucy01
Icon for Advocate I rankAdvocate I
6 months ago
Solved

Add Measures to Slicer

I have a trend chart tracking categories over time, and each category is scored based on three results. For example, Attendance can be scored 'Above Average, Average, and Below Average'.  I would lik...
  • pcoley's avatar
    6 months ago

    Lucy01 

    You can not use a measure in this case. The values must be placed in a column of a table in the model:
    If your data can't be easily unpivoted,  you can use a disconnected table and switched measures.

    1. Create a Disconnected Category Table:
      • Go to Modeling > New Table.
      • Use DAX like this (replace with your actual categories):
         
        Category Table = DATATABLE(
            "Category", STRING,
            {{"Attendance"}, {"Performance"}, {"Other Category"}}
        )
        This creates a simple table with one column: Category.
    2. Create the Slicer:
      • Drag "Category" from the new table to the canvas as a slicer.
      • Set to single-select if desired.
    3. Create Switched Measures for Each Result:
      • Assuming you already have per-category measures (e.g., [Attendance Above], [Attendance Average], [Attendance Below], etc.), create three dynamic measures:
        Above Dynamic = 
        SWITCH(
            SELECTEDVALUE('Category Table'[Category]),
            "Attendance", [Attendance Above],
            "Performance", [Performance Above],
            "Other Category", [Other Category Above],
            BLANK()  // Default if no selection
        )
        Average Dynamic = 
        SWITCH(
            SELECTEDVALUE('Category Table'[Category]),
            "Attendance", [Attendance Average],
            "Performance", [Performance Average],
            "Other Category", [Other Category Average],
            BLANK()
        )
        Below Dynamic = 
        SWITCH(
            SELECTEDVALUE('Category Table'[Category]),
            "Attendance", [Attendance Below],
            "Performance", [Performance Below],
            "Other Category", [Other Category Below],
            BLANK()
        )
         
    4. Create the Trend Chart:
      • Add a Line Chart.
      • Axis: Date/Time Period.
      • Values: Add [Above Dynamic], [Average Dynamic], [Below Dynamic] (they'll appear as separate lines/series).
      • The chart will update based on the slicer selection.

    This method is quick if you have few categories but can become cumbersome with many (long SWITCH statements). To handle "no selection," you could wrap in IF(HASONEVALUE(...), SWITCH(...), [Some Default Measure]).

    I hope this helps. if so please mark it as a solution. kudos are welcome.