Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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 =
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:
(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
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"}
(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
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
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
it would be great if you explain more in details this measure you suggested and how is it going to solve my issue... thanks!
@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?
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |