Forum Discussion
Set FILTER value with variable
Hello.
I'm ex QlikView developer switching to Power BI. Now I'm trying to do some aggregations I've used to do in QlikView. I'm missing badly especially this one - to set filter value in expression using variable. Here is an example:
CALCULATE(
SUM('Customer Transactions'[Amount]);
FILTER('Customer Transactions'; 'Customer Transactions'[Transaction Date]<"Some specific date")
)
In QlikView I was able to define this "specific date" in two ways:
- Based on selection in [Transaction Date] field. In this case "Some specific date" = MAX(Transaction Date). To make this working you should remove [Transaction Date] selection effect in the expression.
- Define variable varDate with empty value. User is able to set value to varDate via calendar object. In this case "Some specific date" is just varDate value.
I've found some solution about point 1 here http://goo.gl/XvvMs9 but it's far too complex. I'm trying to find a way to make point 2 in Power BI. Is there such possibility?
Hi,
I assume your Transactions table is like below.
In DAX, we can’t define a variable for user selection and use it in calculation. You should use slicer to achieve your goal. In your scenario, I think you want to calculate the cumulative total amount. You need to use ALL() function to ignore the current row slicing.
Your measure should be like:
Measure = CALCULATE(sum(Transactions[Amount]), FILTER(ALL(Transactions), Transactions[Transaction Date] <MAX(Transactions[Transaction Date])))
Now we can create a slicer to show this measure. I think you don’t want to have the slicer selection affect visual. You can “Edit Interactions” and select “None” on visual.
And when you show this measure to whole table, you can see that:
Best Regards
Alex
4 Replies
Hi,
I assume your Transactions table is like below.
In DAX, we can’t define a variable for user selection and use it in calculation. You should use slicer to achieve your goal. In your scenario, I think you want to calculate the cumulative total amount. You need to use ALL() function to ignore the current row slicing.
Your measure should be like:
Measure = CALCULATE(sum(Transactions[Amount]), FILTER(ALL(Transactions), Transactions[Transaction Date] <MAX(Transactions[Transaction Date])))
Now we can create a slicer to show this measure. I think you don’t want to have the slicer selection affect visual. You can “Edit Interactions” and select “None” on visual.
And when you show this measure to whole table, you can see that:
Best Regards
Alex
Thank You, Alex. This works for me.
Trying to move part of workload from Qlik as well (due to business need, not because of problems with Qlik itself).
Very hard to map Qlik stuff and way of thinking to M, DAX and Power BI way of thinking, especially Set analysis.
But the lack of variables which can be controlled by user via UI - this is a real bummer in Power BI. It is so useful to have filters, slicers, etc to control variables inside the script.
You can create a variable in powerbi but they are local to the calculation only.
or the other approach you can use is use of parameters in query mode.
You can find the more information in use of variables in this blog post.
https://www.sqlbi.com/articles/variables-in-dax/
