Forum Discussion
Reference Table from DAX
amsrivastavaa You cannot. DAX calculated columns and tables are not dynamic, they are calculated at the time of refresh.
Hi Greg_Deckler ,
Can we have something like this using DAX as detailed below.
Let say I have table Transaction as shown below
| Project | Type | Year | Amount | Total_Amount |
| P-1 | A | 2017 | 100 | 160 |
| P-2 | A | 2017 | 50 | 160 |
| P-3 | A | 2017 | 10 | 160 |
| P-1 | A | 2018 | 20 | 75 |
| P-2 | A | 2018 | 55 | 75 |
| P-1 | A | 2019 | 75 | 250 |
| P-2 | A | 2019 | 85 | 250 |
| P-3 | A | 2019 | 90 | 250 |
Project, Type, Year & Total_Amount is coming from Source however for Total_Amount i have created using below DAX
Total_Amount = CALCULATE(SUM('Transaction'[Amount]),ALLEXCEPT('Transaction','Transaction'[Type],'Transaction'[Year]))
This works well when we have complete set of data but when user selects say Project=P-1 in slicers , then amount of all other Project P-2, P-3 still added in Total_Amount.
I want, when user selects say P-1, then it will only sum amount for P-1 project for Total_Amount measure, similarly, if User Selects P-2 and P-1 both, Total_Amount must be sum of all the amounts of Project P-1 and P-2.
Based on above table.
If user select Project= P-1, Year=2017 then Total_Amount = 100
If user select Project= P-1 and P-2, Year=2017 then Total_Amount = 100+50 = 150.
If user select Project= P-1 ,P-2 and P-3, Year=2017 then Total_Amount = 100+50 +10= 160
Can we define measure (tot_amount) in such a way that it will work on data which is available after filteration.
Thanks
Amit
amsrivastavaa That should be possible:
Total_Amount = CALCULATE(SUM('Transaction'[Amount]),ALLEXCEPT('Transaction','Transaction'[Type],'Transaction'[Year],'Transaction'[Project]))Hi Greg_Deckler ,
Below is the layout of the data
Tot_Amount =
Total_Amount = CALCULATE(SUM('Transaction'[Amount]),ALLEXCEPT('Transaction','Transaction'[Type],'Transaction'[Year])Total_Amount =
Total_Amount = CALCULATE(SUM('Transaction'[Amount]),ALLEXCEPT('Transaction','Transaction'[Type],'Transaction'[Year],'Transaction'[Project]))Filter available : Project
First Instance : When user have selected all the Project, then Total sum for Credit across all the project for Year 2017 is 1210 i.e. Tot_Amount value.
Second Instance : When user have selected Project as P-1 and P-2 only, as shown below
Then, Total Sum of Amount for Credit across all the project for Year 2017 must be 1000 +100=1100.
But none of the above DAX gave me correct result.
I need DAX which will calculate Total Amount based on what data available after filteration
Please suggest!!
Thanks
Amit
amsrivastavaa See attached PBIX file below signature. The measure seems to work for both of your scenarios. Returns 1210 if just Year = 2017 and 1,100 if P1 and P2 and Year = 2017. You have to use a measure, you cannot use a column.