Forum Discussion

aj1107's avatar
aj1107
Icon for Advocate I rankAdvocate I
9 years ago

Passing Slicer Selection value in DAX Filter

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(ta...
Need Help
aamer's avatar
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.
MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

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.

  • aamer's avatar
    aamer
    Regular Visitor
    5 years ago

    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?
  • DaveNZ's avatar
    DaveNZ
    New Member
    3 years ago

    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!