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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jpereztang
Helper I
Helper I

Related filtering with years

Hi everyone,

 

I hope u can help me with this. 

 

These are my two tables.

 

sales.png

 

time.png

 

Sales ant Time and I have a measure that is: 

 

TotSales = CALCULATE(
					SUM(Sales[SalesAmount])
)

My canvas:

canvas.png

 

According my filter i get 13061 that are all of Jun2017 but I want only sales when OriginYear is the same year i filter.

 

wwwwwww.png

 

Do you have an idea how to achieve it? 

 

Thank you !!

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

The following measure should work:

 

OriginSales =
VAR SlicerYear =
SELECTEDVALUE ( CalendarTable[Year] )
RETURN
CALCULATE ( [TotSales], SalesTable[Origin Year] = SlicerYear )

 

We create a variable called SlicerYear that stores the current value from the slicer.

We then modify the filter context by using CALCULATE(), and specify that we want SalesTable[OriginYear] to equal the stored SlicerYear value.

 

All the best,

 

~ Chris H

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

 

I do not get it.  How did 13,061 comes about.  Does number 4 represent January?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur @Anonymous

 

Sorry for mistake this is what i get when select Jan 2017

 

tot sales.png

 

Finally i could get the value. 

 

Thanks a lot ! 

Anonymous
Not applicable

Hi Ashish -

 

I don't think that 2nd image actually corresponds to the number 13,061.  I believe the OP meant that the filter context generated by his / her slicers was returning the larger box.  However, the desired result is only a subset of that filter context, such that the Origin Year column matches the Year value from the slicer.

 

I believe my measure posted previously will accomplish this.

Anonymous
Not applicable

The following measure should work:

 

OriginSales =
VAR SlicerYear =
SELECTEDVALUE ( CalendarTable[Year] )
RETURN
CALCULATE ( [TotSales], SalesTable[Origin Year] = SlicerYear )

 

We create a variable called SlicerYear that stores the current value from the slicer.

We then modify the filter context by using CALCULATE(), and specify that we want SalesTable[OriginYear] to equal the stored SlicerYear value.

 

All the best,

 

~ Chris H

Hi @Anonymous do you know how can i replace SELECTEDVALUE for a valid function in a tabular model on SSAS ? Because it doesn't recognize SELECTEDVALUE as a valid function. 

 

Anonymous
Not applicable

You must have an earlier version of SSAS, no worries.

 

Replace SELECTEDVALUE() with MIN().  It will accomplish the same thing; it will return the smallest year value in the current filter context.

 

If nothing is selected in the slicer, it will return the first year.  If the slicer is set to Year = 2017, then the only value in the current filter context is 2017, so MIN() will return 2017.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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