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
Anonymous
Not applicable

Dynamic Measures based on filter selection

I have Sales Column and I created a YTD measure using that column. I want to accomplish the following.

 

Show YTD or Sum of Sales when a date value is selected. Is there a way to accomplish this, like create a dummy filter(like groupby) with values YTD and Monthly and when date value is selected with one of these group by filter values it would select corresponding value. For Ex in my case when YTD is selected I want see YTD measure and sum of sales when Monthly is selected.

 

 

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@Anonymous

 

1. Create a New Table with

 

Calc

YTD

Monthly

 

2. Then Use a Slicer with this column

 

3. Create a measure

 

Sales=Switch(Values(Newtable[Calc]),"YTD",MeasureofYTD,"Monthly",MeasureofSumSales)

 

 




Lima - Peru

View solution in original post

2 REPLIES 2
KHorseman
Community Champion
Community Champion

I just finished a straight hour of writing a series of measures that do exactly this. Here's my method:

 

Use "Enter Data" to create a new table. Call it something like "MeasureBehavior" and make a column called "Stat". You can call it whatever you want, but that's what I call it. Stat is the name of the method you want to use, so Monthly, YTD, whatever else...

 

Then your measures will take the following form:

 

Measure = IF(
	HASONEVALUE(MeasureBehavior[Stat]),
	SWITCH(
		FIRSTNONBLANK(MeasureBehavior[Stat], 1),
		"Monthly", SUM(TableName[ColumnName]),
		"YTD", CALCULATE(
			SUM(TableName[ColumnName]),
			DATESYTD(DateTable[Date])
		),
		BLANK()
	),
	BLANK()
)

The BLANK() functions are defaults for when conditions are not met. The first one is if there is only one stat selected but it's not either Monthly or YTD. This condition should never actually happen but I think it's best to give SWITCH a failsafe blank as a general rule. The last BLANK() is for cases when more than one stat is selected (or when the slicer has no selection, which is the same thing). You may prefer to use the plain monthly sum here, so the default will always be monthly unless YTD is explicitly selected. Up to you.

 

Oh, then of course you would add a slicer for MeasureBehavior[Stat] to the report.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Vvelarde
Community Champion
Community Champion

@Anonymous

 

1. Create a New Table with

 

Calc

YTD

Monthly

 

2. Then Use a Slicer with this column

 

3. Create a measure

 

Sales=Switch(Values(Newtable[Calc]),"YTD",MeasureofYTD,"Monthly",MeasureofSumSales)

 

 




Lima - Peru

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.

Top Solution Authors