Passing Slicer Selection value in DAX Filter

[aj1107]

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.

[Sravanisrinivas]

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.

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[Sravanisrinivas]

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.

[Sravanisrinivas]

if i select 2021,need 2017 to 2021 data

[MFelix]

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?

[aamer]

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]

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]

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.

[MFelix]

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.

[DaveNZ]

Felix - this is the correct way to do it. I was stumped on this also as intuitively you would be expected to work with the selected value expression directly. Passing in into a variable is the way to solve this. Much appreicated!

[aamer]

Thanks again for your reply..

I need the calculated table to group the transactions per customer. A measure will only calculate the transactions for the selected customer but if I need to do it for all customers I bleieve it has to be in a calculated column. This way I would be able to plot transaction count per customer.

My first step was to summarize the transactions table as follows:

CustomerTransactionsCount = SUMMARIZECOLUMNS(Revenue_Lines[Customers])

Then I would add the calculated columns.

Is there another way to do this calculations?

[v-jiascu-msft]

@aj1107

Hi,

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

Best Regards!

Dale

[Jack09]

Hi,

Is it possible when there is no relationship between two table?

Thanks in Advance!

Jaymin

[MFelix]

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]

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]

@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