Forum Discussion
Reference Table from DAX
Hi Guys!!
My requirment is to create one Reference table using DAX in such a way that if there is any change in base table it will also get reflected in Reference table as well.
Lets say, I have one Filter F-1 and One Table visual Table-1, let say User saelected any value in F-1 and data got filtered in Table-1, i need to create Table-2 that refrenced Table-1, that is as soon as F-1 filtered the Table-1, Table-2 also get filtered and always have same set of data as Table-1 has.
Please suggest how can we do this via DAX
Thanks
Amit Srivastava
14 Replies
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 amsrivastavaa ,
try this maybe it works
Total_Amount = CALCULATE(SUM('Facts17'[Amount]),ALLSELECTED(Facts17))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi mangaus1111
Thats giving me sum of all the records, i need sum based on Type and year for whatever data left after applying filter.
Thanks
Amit
