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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DPE_Fabulous
Frequent Visitor

How to filter X-Axis (date) depending on a slicer

Hi everybody,

 

I'm doing a basic stacked column chart and I wanted to change the X-Axis (based on a date field) depending on a slicer (12 last months or 6 last months). This slicer will filter the Date X-Axis in order to show only 6 months or 12 months.

I've created a Table called "Temporalité" with 2 values. It's going to be a text slicer which function is to filter the Date Axis (Annuelle = 12 months and Semestrielle = 6 months)

 

DateDynamic = 
var _MaxDate = CALCULATE(MAX(Table[Date]), ALL('Table'))
var _MinDate6 = EDATE(_MaxDate,-6)+1
var _MinDate12 = EDATE(_MaxDate,-12)+1

return
SWITCH(
    TRUE(),
    VALUES('pTemporalité'[Type de temporalité])="Annuelle" && MAX(Table[Date]) <= _MaxDate && MAX(Table[Date]) > _MinDate12, ??????????,
    VALUES('pTemporalité'[Type de temporalité])="Semestrielle" && MAX(Table[Date]) <= _MaxDate && MAX(Table[Date]) > _MinDate6,  ????????????,
    BLANK())

 

I'm stuck with this problem... 

 

I hope that somebody will help me!

 

Thank you

 

2 REPLIES 2
amitchandak
Super User
Super User

@DPE_Fabulous , if you select one month or date and then want to display a trend of 6 or 12 months you need a slicer on an independent date table

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Hi @amitchandak ,

It doesn't work for my issue because I use a lot of different slicers.

 

Here's a screenshot: 

DPE_Fabulous_0-1666014421134.png

I need that I can change my variable with a slicer to change from _Min6 to _Min12, in order to change the number of TRUE in my list (which is becoming, afterwards, a range of date for my bar chart).

 

The aim is to keep only the "blue" one (Min6 and Min12 below): 

DPE_Fabulous_1-1666014816269.png

DPE_Fabulous_2-1666014852559.png

 

P.S. In green, when I use the VAR in the return, it works. But not the VAR in the red rectangle. Why? 

The error shown is: A table of multiple values was supplied when a single value was expected

DPE_Fabulous_0-1666079265827.png

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors