Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Dim slicer - replace Month with Year (need help)

Hi all

I want to be able to change my chart dim from month to a year (be able to view the chart by years or by months).

I have created a dim table for the slicer: 

AXIS SLICER =

UNION(
CROSSJOIN(ROW("Axis", "Year"), VALUES('Date'[Year])),
CROSSJOIN(ROW("Axis", "Month"), VALUES('Date'[Month]))
)
This table contains now:
Axis         Year
Year2010
Year2011
Year2012
Year2013
Year2014
Year2015
Year2016
Year2017
Year2018
Year2019
MonthJan
MonthFeb
MonthMarch
MonthApril

And so on till December..

 
Now, my chart has 'Year' column in the axis, and my slicer has 'Month' and 'Year' buttons while in the category field it's 'Axis' column from the table I have created. 
My problem is that this solution not calculating right the values in the chart. My values in the chart are some type of average, the same value for each Year/Month. 
Am I missing something?
Thank you in advance. 
 
1 REPLY 1
MFelix
Super User
Super User

Hi @Anonymous ,

 

Not sure how you have your data setup but since you are making this dimension table to change the axis you need to make a measure for each of your calculations.

 

When you have two tables that are not related and you use one of them on your visuals to give context (in this case the axis) and the values are from another table you will get the full sum (average, count,...) on the table of the values since there is no relation between axis and values so it pick up everything on the value side.

 

Based on this create a measure for the calculation of the YEARS / MONTHS and using a switch measure return the value to your chart.

 

Check this post with an explanation how to achieve this.

 

I have also authored a post in the community with 3 options to get this made, please also see different possibilities to achieve this.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.