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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Using slicer to change the value of a measure

Hi,

 

I am trying to visualise the number of profitable days vs non profitable days per month. To this, I want to add an adjusteable measure to what defines a profitable day. Please see the screenshot:

kkiw_0-1601660403126.png

 

I would like to be able to change the value of the measure using a slicer to change the definition of what counts as a profitable day (e.g. if I want to set the limit at 500). However, it seems like it is not possible to use a slicer for a measure. It would be highly appreciated to receive some assistance in this. Thanks!

1 ACCEPTED SOLUTION
stevedep
Memorable Member
Memorable Member

Hi,

 

I think you are looking for this:

 

 

_NumberOfNotProfitableDays = SUMX(SUMMARIZE(tblSales,[_Day], "C", IF(SUM(tblSales[sales_amount]) <= Threshold_profitableday[Threshold_profitableday Value],1,BLANK())),[C])

_NumberOfProfitableDays = SUMX(SUMMARIZE(tblSales,[_Day], "C", IF(SUM(tblSales[sales_amount]) > Threshold_profitableday[Threshold_profitableday Value],1,BLANK())),[C])

_Day = DAY(tblSales[sales_date_time])

 

 

As seen here:

salesthreshold.gif

Link to file

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards, 
Steve. 

 

Testdata generated with:

 

tblSales = 
var _tbl = 
	SELECTCOLUMNS(
		CROSSJOIN(
			ROW("test", DATE(2020, 10,3)),
			GENERATESERIES(1,60,1)
			), "StartDate", [test], "Increment", [Value])

var _dates = 
ADDCOLUMNS(	_tbl, "NewDate", [StartDate]+[Increment]) 

return
SELECTCOLUMNS(
    ADDCOLUMNS(
        CROSSJOIN(
        _dates, GENERATESERIES(1,10,1)), "open_time", [NewDate] +  NORM.INV(RAND(), 0.5 + ([Increment]/100), 0.08), "sales_ref", CONVERT( UNICHAR(RANDBETWEEN(65,90)),STRING) & CONVERT( UNICHAR(RANDBETWEEN(65,90)),STRING) & CONVERT( UNICHAR(RANDBETWEEN(65,90)),STRING), "sales_amount", NORM.INV(RAND(), [Value], [Value]/10)
        ), "sales_date_time", [open_time], "sales_ref", [sales_ref], "sales_amount", [sales_amount])

 

View solution in original post

5 REPLIES 5
stevedep
Memorable Member
Memorable Member

Hi,

 

I think you are looking for this:

 

 

_NumberOfNotProfitableDays = SUMX(SUMMARIZE(tblSales,[_Day], "C", IF(SUM(tblSales[sales_amount]) <= Threshold_profitableday[Threshold_profitableday Value],1,BLANK())),[C])

_NumberOfProfitableDays = SUMX(SUMMARIZE(tblSales,[_Day], "C", IF(SUM(tblSales[sales_amount]) > Threshold_profitableday[Threshold_profitableday Value],1,BLANK())),[C])

_Day = DAY(tblSales[sales_date_time])

 

 

As seen here:

salesthreshold.gif

Link to file

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards, 
Steve. 

 

Testdata generated with:

 

tblSales = 
var _tbl = 
	SELECTCOLUMNS(
		CROSSJOIN(
			ROW("test", DATE(2020, 10,3)),
			GENERATESERIES(1,60,1)
			), "StartDate", [test], "Increment", [Value])

var _dates = 
ADDCOLUMNS(	_tbl, "NewDate", [StartDate]+[Increment]) 

return
SELECTCOLUMNS(
    ADDCOLUMNS(
        CROSSJOIN(
        _dates, GENERATESERIES(1,10,1)), "open_time", [NewDate] +  NORM.INV(RAND(), 0.5 + ([Increment]/100), 0.08), "sales_ref", CONVERT( UNICHAR(RANDBETWEEN(65,90)),STRING) & CONVERT( UNICHAR(RANDBETWEEN(65,90)),STRING) & CONVERT( UNICHAR(RANDBETWEEN(65,90)),STRING), "sales_amount", NORM.INV(RAND(), [Value], [Value]/10)
        ), "sales_date_time", [open_time], "sales_ref", [sales_ref], "sales_amount", [sales_amount])

 

Anonymous
Not applicable

Thank you, that was awesome! 🙂

daxer-almighty
Solution Sage
Solution Sage

"However, it seems like it is not possible to use a slicer for a measure."

This is a joke, right? 🙂
Anonymous
Not applicable

Im really really new to Power BI, please be kind 😛

 

Do you know any other solution to this?

To harvest values from slicers you can use the SELECTEDVALUE function and use it in a measure. Please refer to this: https://dax.guide/selectedvalue/

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.