I've a requirement to diplay last 6month (in X-axis) for a given month selected in Slicer. As a 1st step, I can get the 6month by restricting directly in DAX. =calculate(count(column),filter(table1,cdate>=eomonth(today(),-6) && cdate<=eomonth(today(),-1))) Above DAX by default diaplay 6month data in chart. Next step, i need to get the value from selected value from slicer. Slicer will have MMMYY value. Eg., if I select Apr17 in slicer, then the chart should display from Oct16 to Mar17. Can anyone guide me on how to pass the selected month slicer value to underline DAX part. Thank You.

aj1107 Hi, Maybe you want the dates less than the selected date in the first post while you change it in the last post. This won’t be problem, just something about the operator. According to last post, you can try this formula.IDCountNew =IF ( MAX ( Test[cdate] ) < MAX ( ForSlicer[cdate] ), BLANK (), CALCULATE ( COUNT ( Test[id] ), FILTER ( ALL ( Test ), Test[cdate] >= MAX ( ForSlicer[cdate] ) && Test[cdate] >= MAX ( Test[cdate] ) ) )) Best Regards!Dale

aj1107 Hi, Did you solve your problem? What else can I do for you? Best Regards!Dale

Hi aamer , This can be done using measures. I don't know how your data is structure but I assume is something similar to the one below: Date Tranx_NUMBER Column1 Column2 .... ColumnN If you have a calendar table then you can add a measure for curent year and the previous 2 year that way you would be abble to make the count and further calculations. Measures would be similar to this: TransSelectedYear = DISTINCTCOUNT (Revenue_Lines[TRX_NUMBER]) TransSelectedYear - 1 = VAR Year_Selection = SELECTEDVALUE(DAte[Year]) Return CALCULATE([TransselectedYear], FILTER(ALL(Date[Year]), Date[Year )= Year_Selection - 1])) TransSelectedYear - 2 = VAR Year_Selection = SELECTEDVALUE(DAte[Year]) Return CALCULATE([TransselectedYear], FILTER(ALL(Date[Year]), Date[Year )= Year_Selection - 2])) Now this measures can be used to make the calculations. If you share a sample data I can provide a better example. No need to create additional tables on your model except the date table.

Hi aj1107, I don't know what is the column you are using on your slicer but assuming its column CDATE and you only have one single value select on the slicer you can use this formula: Date_selected = Max(Table1(CDATE)) Regards,MFelix

Thanks for the reply.I tried the soultion but not getting the expected result. Slicer : cdatecalcmeasure1 : Date_selected=max(cdate)calcmeasuer2 : idcount=calculate(count(id),filter(table,cdate>=date_selected) id, plan, cdate1, ab, 4/30/20172, wr, 3/31/20173, qw, 2/28/20174, we, 3/31/2017 When i select slicer, it gives count only for the selected date and not for the filter range. 4/30/2017 - 13/31/2017 -22/28/2017 - 1Expected result 4/30/2017 - 13/31/2017 -32/28/2017 - 4 Not sure am I missing anything.

Passing Slicer Selection value in DAX Filter

16 Replies

v-jiascu-msft
Microsoft Employee
9 years ago
aj1107

Hi,

Did you solve your problem? What else can I do for you?

Best Regards!
Dale
- Jack09
  Frequent Visitor
  8 years ago
  Hi,
  
  Is it possible when there is no relationship between two table?
  
  Thanks in Advance!
  Jaymin
MFelix
Super User
9 years ago
Hi aj1107,

I don't know what is the column you are using on your slicer but assuming its column CDATE and you only have one single value select on the slicer you can use this formula:

Date_selected = Max(Table1(CDATE))

Regards,
MFelix
- aj1107
  Advocate I
  9 years ago
  Thanks for the reply.
  I tried the soultion but not getting the expected result.
  
  Slicer : cdate
  calcmeasure1 : Date_selected=max(cdate)
  calcmeasuer2 : idcount=calculate(count(id),filter(table,cdate>=date_selected)
  
  id, plan, cdate
  1, ab, 4/30/2017
  2, wr, 3/31/2017
  3, qw, 2/28/2017
  4, we, 3/31/2017
  
  When i select slicer, it gives count only for the selected date and not for the filter range.
  
  4/30/2017 - 1
  3/31/2017 -2
  2/28/2017 - 1
  Expected result
  
  4/30/2017 - 1
  3/31/2017 -3
  2/28/2017 - 4
  
  Not sure am I missing anything.
  - v-jiascu-msft
    Microsoft Employee
    9 years ago
    aj1107
    
    Hi,
    
    Maybe you want the dates less than the selected date in the first post while you change it in the last post. This won’t be problem, just something about the operator. According to last post, you can try this formula.
    IDCountNew =
    IF (
        MAX ( Test[cdate] ) < MAX ( ForSlicer[cdate] ),
        BLANK (),
        CALCULATE (
            COUNT ( Test[id] ),
            FILTER (
                ALL ( Test ),
                Test[cdate] >= MAX ( ForSlicer[cdate] )
                    && Test[cdate] >= MAX ( Test[cdate] )
            )
        )
    )
    
    Best Regards!
    Dale
aamer
Regular Visitor
5 years ago
I have a similar issue..
I have TableA with transactional data, when filtered to get rows that belong to a specific year I used the following
MEASURE: SelectedYear = selectedvalue(Years[year]) // returns the correct selected year in a slicer
NewTable = Filter(TableA, Year(TransDate) = SelectedYear)
it returns an empty table with no rows
If I do a hardcode filter like the following it works fine
NewTable - Filter(TableA, Year(TransDate) = 2019)
I do not know what went wrong.
- MFelix
  Super User
  5 years ago
  Hi aamer ,
  
  You cannot use measures to create tables in DAX measure are based on context and when you pickpup the SELECTEDVALUE on a table the value you get return is a table of multiple values so nothing is returned in your new table.
  
  What is the purpose that you want to achieve?
  
  Why are you creating a new table?
  - aamer
    Regular Visitor
    5 years ago
    Thanks Felix for your reply.
    I need to count the number of transactions per customer every year, then based on the selected year, we use the count of that year and the 2 previous years to determine certain status for the customer. So year selection must be dynamic and based on a slicer. Transaction count will be stored in a column (3 columns actually) for the selected year and the 2 prevoius ones.
    In the calculated column, I use the following
    TransSelectedYear = calculate( DISTINCTCOUNT (Revenue_Lines[TRX_NUMBER]), filter(Revenue_Lines, Revenue_Lines[TRX_Year] = [SelectedYear]))
    
    My other solution is to get the TransactionCount explicitly per year (that works), then go through a huge decision structure statement to select the proper columns based on the year selection (should work but not tested yet), which I am trying to avoid.
Sravanisrinivas
New Member
4 years ago
Hi,
how to select by default last five years data in slicer...
for ex if i am selecting 2020 in my slicer, by default i want to show 2016-2020 data.
can you please provide me the dax code.
- MFelix
  Super User
  4 years ago
  Hi Sravanisrinivas ,
  
  Do you want to show the default values on the slicer of the last 5 years or your calculations should be the ones in the last 5 years?
- Ashish_Mathur
  Super User
  4 years ago
  Hi,
  
  See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
  - Sravanisrinivas
    New Member
    4 years ago
    Hi,
    in my slicer i selected 2022, but my requirement is when i select 2022 need to show 2018-2022 data in my trend visuals...what is the dax code r how slove slove this issue.

Forum Discussion

Passing Slicer Selection value in DAX Filter

16 Replies

Related content

Selected slicer

Slicer default selection

Single select slicer

Between date slicer - calender selection

Slicer selection