Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
Solved! Go to Solution.
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
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |