cancel
Showing results for
Did you mean:
Helper IV

## Slicer of a measure larger than zero or not

Hi all,
i wish to create a slicer out of a measure "Sales Last X Months" which is true/false.
The "Last X Months" period is changing according to user's selection.
My "Sales Last X Months" measure is:
Sales Last X Months = CALCULATE(sum(Sales),DATESINPERIOD('Calendar Table'[Date],max('Last Refresh'[LastRefresh]),-'Last X Months'[Last X Months Value],MONTH))+0

i have like 7-10 additional similar measures i wish to convert to slicers the same way.

Amit

9 REPLIES 9
Community Support

Hi @akfir ,

According to your description, You want to create a slicer that displays sales for the previous x months based on the slicer selection. Right?

Here are the steps you can follow：

(1)This is my test data:

(2) We need to create a date table based on the date column in our "Last Refresh" table:  (Click New Table and input the dax as follows)

``````Calendar Table =
CALENDAR (
FIRSTDATE ( 'Last Refresh'[LastRefresh] ),
LASTDATE ( 'Last Refresh'[LastRefresh] )
),
"year", YEAR ( [Date] ),
"quarter", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ),
"month", MONTH ( [Date] ),
"weeknum", WEEKNUM ( [Date] ),
"year & quarter",
YEAR ( [date] ) & "Q"
& ROUNDUP ( MONTH ( [Date] ) / 3, 0 ),
"year & month",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] ),
"year & weeknum",
YEAR ( [Date] ) * 100
+ WEEKNUM ( [Date] ),
"weekday", WEEKDAY ( [Date] )
)``````

We need to create a one-to-many relationship between the two tables:

(3)We can use New parameter(What if) to create a slice:

(4)We can create a measure Total Sales .We can put [Total Sales] and Calendar Table[year & month] in the visual:

Total Sales = SUM('Last Refresh'[Sales])

(5)We can create a measure to determine which months to display : isDisplay

``````isDisplay =
VAR _slice =
SELECTEDVALUE ( 'Parameter'[Parameter] )
VAR _max_date =
MAXX ( ALLSELECTED ( 'Last Refresh' ), 'Last Refresh'[LastRefresh] )
RETURN
IF (
MAX ( 'Last Refresh'[LastRefresh] ) > EOMONTH ( _max_date, - _slice ),
1,
0
)``````

(6)We can put the [isDisplay] in the Filter on this visual and configure it:

(7)The result is as follows:

If this method does not meet your needs, you can provide us your sample data without sensitive data, or detailed input and output sample data, so that we can better help for you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

i already have what you mentioned.
because i can not use that measure in a slicer, i just need to create a slicer with true / false selection:
true = sales last x months >0
false = sales last x months = 0
the x is being selected by the user in an existing parameter, so it should be based on that.

Community Support

Hi @akfir ,

According to your description You want to create a slicer (where the values are "True" and "False"), and the slicer is based on some measure you created..Right?

True= [sales last x months] >0

False= [sales last x months] =0

Here are the steps you can follow：

(1) We can click new table by DAX, and we need to put the [Value] filed in the slice:

``Table = {"True","False"}``

(2)Assume that the measure [sales last x months] had been created

(3)Then we create another measure Last x Month2” and we put it in the visual:

``````Last x Month2 =
VAR _slice =
SELECTEDVALUE ( 'Table'[Value] )
RETURN
SWITCH (
TRUE (),
_slice = "True", IF ( [sales last x months] > 0, [sales last x months], BLANK () ),
_slice = "False", IF ( [sales last x months] = 0, [sales last x months], BLANK () ),
[sales last x months]
)``````

(4) Then you can use the slicer to determine the measure you created earlier:

If this method does not meet your needs, you can provide us your sample data without sensitive data, or detailed input and output sample data, so that we can better help for you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

Thanks for your assistance. thats definitely a nice trick i will use in the future, but i was meaning something a little bit different. please have a look at the sample snap shot i have attached here.
means - i dont neccesarily want to use this inside a visual but to slice different dimensions of other data (in my example - # Customers).
hope it is clearer now.

Community Support

Hi  @akfir ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

Hi @akfir ,

According to your description, You want to filter and display other data by the calculation of your measure [Sales Last X Months]. Right?

First, measures cannot be placed in slicers, and slicers cannot be automatically selected. A measure always returns a calculated value in the current filter context, so we cannot create a slicer based on the measure. If we need to filter the values of other fields based on the value returned by the measure value. We can directly add the judgment condition in the IF() function.

Create the corresponding measure:

``Customers 2 = IF ( [Sales Last X Months] > 0, [ResultTrue], [ResultFalse] )``

If this method does not meet your needs, you can delete the private data and provide sample data in a table or .pbix file, and describe your needs in detail, so that we can better help you solve the problem.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

it would be great if you explain more in details this measure you suggested and how is it going to solve my issue... thanks!

Super User

@akfir , You can use what-if parameter and you can use what-if measure in place of -'Last X Months'[Last X Months Value]

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

Helper IV

i will check this out, but it does not resolve my issue. any idea how can i solve this one?

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors