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
akfir
Helper V
Helper V

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.

Thanks in advance,
Amit

9 REPLIES 9
v-yiruan-msft
Community Support
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:

yingyinr_0-1662019647755.png

 

(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 =
ADDCOLUMNS (
    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:

yingyinr_1-1662019647763.png

 

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

yingyinr_2-1662019647771.png

yingyinr_3-1662019647772.png

 

(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])

yingyinr_4-1662019647774.png

(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:

yingyinr_5-1662019647779.png

 

(7)The result is as follows:

yingyinr_7-1662019799361.png

 

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.

thanks for your detailed answer.
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.


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"}

yingyinr_1-1662080929326.png

 

(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:

yingyinr_3-1662081852810.png

 

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.

akfir_0-1662150674880.png

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.

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.

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.

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

amitchandak
Super User
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

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

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.

Top Solution Authors