Dynamic X axis on charts

posted in DAX, Visuals on by
Share The Knowledge

Slicer value dynamically changes the x axis on a chart.

Requirement

Selecting ‘Month’ in the slicer displays month values and amounts on x axis.

Selecting ‘WeekDay’ in the slicer displays weekday values and amounts on x axis.

Selecting ‘Year’ in the slicer displays year values and amounts on x axis.

Files

0029 Slicer changes X-axis_Download pbix file

Concepts

These concepts helped me get started:

  • X axis values come from a column in a table (can’t be a measure)
  • a slicer selection can’t be used in a table but it can filter visuals

I envisioned a solution like this:

Slicer values from field DateType. As slicer = Month the viz x axis shows 1 to 12. Change to WeekDay to see 1 to 7. Viz sums field AggAmount.

How can I create table ‘X-axis Summarized’? On paper I drew a table with 3 columns and 3 groups of rows.

The table’s 3 columns. Column names & values:

  • DateType – Month, WeekDay, Year
  • X-axis – Month: 1 to 12, Quarter: 1 to 4, Year: 2018 to 2021
  • AggAmount – aggregation of field Sale Amount (Table ‘Sales’)

The table’s 3 groups of rows:

  • 12 rows for months
  • 7 rows for weekday
  • 4 rows for years

Solution

Final DAX Code

This DAX creates aggregated table ‘X-axis Summarized’ later used in slicer and viz:

X-axis Summarized = 
UNION(
//YEAR ROWS
 SUMMARIZE(
   //ADD COLUMNS, NO AGGRETATION YET 
   ADDCOLUMNS(Sales,"DateType","Year","X-Axis",YEAR(Sales[Date])),

   //SUMMARIZE ADDS THE AGGRETATION   
   [X-Axis],[DateType],"AggAmount",SUM(Sales[Sale Amount])),         
 
//MONTH ROWS
 SUMMARIZE(
   //ADD COLUMNS, NO AGGRETATION YET
   ADDCOLUMNS(Sales,"DateType","Month","X-Axis",MONTH(Sales[Date])),
 
   //SUMMARIZE ADDS THE AGGRETATION
   [X-Axis],[DateType],"AggAmount",SUM(Sales[Sale Amount])),         

//WEEKDAY ROWS
 SUMMARIZE(
   //ADD COLUMNS, NO AGGRETATION YET
   ADDCOLUMNS(Sales,"DateType","WeekDay","X-Axis",WEEKDAY(Sales[Date])), 

   //SUMMARIZE ADDS THE AGGRETATION
   [X-Axis],[DateType],"AggAmount",SUM(Sales[Sale Amount]))              
)

Now I’ll review this step by step starting with creating rows for Year.

Step 1 Add Columns

ADDCOLUMNS creates a new table based on table Sales and adding new columns DateType and X-Axis. DateType repeats text “Year”, X-Axis extracts year from field Date. We still have 95176 rows just like referenced table Sales.

X-axis Summarized test = 
ADDCOLUMNS(Sales,"DateType","Year","X-Axis",YEAR(Sales[Date]))
New table has all 3 columns from table Sales and 2 new columns.

Step 2 Aggregate

Aggregate Step 1 rows by Year. SUMMARIZE function aggregates. ADDCOLUMNS creates table, group by X-Axis & DateType, sum Sale Amount.

X-axis Summarized Step2 = 
SUMMARIZE(
ADDCOLUMNS(Sales,"DateType","Year","X-Axis",YEAR(Sales[Date])),   
[X-Axis],[DateType],"AggAmount",SUM(Sales[Sale Amount]))

This creates 4 rows as we have years 2018, 2019, 2020, 2021.

DAX code for new table creates the rows below.

Step 3 Union

Repeat Step 2 for Month and Weekday, stack all 3 using function UNION:

X-axis Summarized = 
UNION(
//YEAR ROWS
 SUMMARIZE(
   ADDCOLUMNS(Sales,"DateType","Year","X-Axis",YEAR(Sales[Date])),   
   [X-Axis],[DateType],"AggAmount",SUM(Sales[Sale Amount])),         
 
//MONTH ROWS
 SUMMARIZE(
   ADDCOLUMNS(Sales,"DateType","Month","X-Axis",MONTH(Sales[Date])), 
   [X-Axis],[DateType],"AggAmount",SUM(Sales[Sale Amount])),         

//WEEKDAY ROWS
 SUMMARIZE(
   ADDCOLUMNS(Sales,"DateType","WeekDay","X-Axis",WEEKDAY(Sales[Date])), 
   [X-Axis],[DateType],"AggAmount",SUM(Sales[Sale Amount]))              
)

This final table could also be created using variables like this:

X-axis Summarized v2 = 
//YEAR ROWS
 var yearrows =
 SUMMARIZE(
   ADDCOLUMNS(Sales,"DateType","Year","X-Axis",YEAR(Sales[Date])),   
   [X-Axis],[DateType],"AggAmount",SUM(Sales[Sale Amount]))         
 
//MONTH ROWS
var monthrows =
 SUMMARIZE(
   ADDCOLUMNS(Sales,"DateType","Month","X-Axis",MONTH(Sales[Date])), 
   [X-Axis],[DateType],"AggAmount",SUM(Sales[Sale Amount]))         

//WEEKDAY ROWS
var weekdayrows =
 SUMMARIZE(
   ADDCOLUMNS(Sales,"DateType","WeekDay","X-Axis",WEEKDAY(Sales[Date])), 
   [X-Axis],[DateType],"AggAmount",SUM(Sales[Sale Amount]))              

RETURN
UNION(yearrows, monthrows, weekdayrows)

Alternative Solution

I solved this without looking online. Later I was curious about other solutions. I finally found a solution from Radacad.com for the exact same requirement.

It’s an entirely different way to solve it. It combines these features:

  • inactive relationships
  • disconnected table
  • function USERELATIONSHIP.

I highly recommend reviewing it.


Share The Knowledge