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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Canada321321
Regular Visitor

Slicer not interacting with Measure

In the following scenario, does anyone know what changes I need to make to allow the slicer to interact as intended?

 

Here's an example table. I want to display a sum of year to date amounts, filtered to account 4, with a slicer allowing the user to toggle between divisions 1 & 2:

 

table.jpg

 

My measure formula is as follows, and it calculates in total properly and responds to date slicing without issue. 

 

measure.jpg

 

my problem is that it won't react to a slicer by division. Any selection in the slicer is ignored.

 

visual.jpg

I assume the issue is with my YTD REVENUE measure details, but I'm uncertain exactly what.

1 ACCEPTED SOLUTION

@Canada321321

 

Hi, try with this:

 

YTDRevenue =
CALCULATE (
    SUM ( Sheet1[Amount] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[Division] ),
        Sheet1[Date] <= MAX ( Sheet1[Date] )
            && Sheet1[Account] = 4
    )
)



Lima - Peru

View solution in original post

5 REPLIES 5
Canada321321
Regular Visitor

Thanks for all the quick replies, Vvelarde's answer does solve my issue. Phil's answer also works for me, except when there's nothing selected on the slicer, at which point it displays incorrectly. Very much appreciated though!

Phil_Seamark
Employee
Employee

Give this a crack.  I had to modify some data to get difference between them

 

YTD Revenue = CALCULATE(SUM('Sheet1'[Amount]),
	FILTER(
		ALL(Sheet1),
		'Sheet1'[date]<=MAX('Sheet1'[date]) 
		&& 'Sheet1'[Division] = MAX('Sheet1'[Division])
		),
		
		FILTER(all('Sheet1'),Sheet1[Account]=4)
		)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark & @Vvelarde are way smarter than me so listen to them

Try changing your ALL(Sheet1) to specify ALL(Sheet1[Date]) or ALL(Sheet1[Account]) for the second part.

 

Your measure effectively overrides the filter context of Division because your ALL argument indicates the entire table.

@Canada321321

 

Hi, try with this:

 

YTDRevenue =
CALCULATE (
    SUM ( Sheet1[Amount] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[Division] ),
        Sheet1[Date] <= MAX ( Sheet1[Date] )
            && Sheet1[Account] = 4
    )
)



Lima - Peru

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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