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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.