Forum Discussion

papanovn's avatar
papanovn
Frequent Visitor
10 years ago
Solved

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 ...
Need Help
  • AlexChen's avatar
    AlexChen
    10 years ago

    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

     

     

     

papanovn's avatar
papanovn
Frequent Visitor
10 years ago

Thank You, Alex. This works for me.

vyacheslavg's avatar
vyacheslavg
Helper II
9 years ago

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.