Forum Discussion

amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago

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 h...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

amsrivastavaa You cannot. DAX calculated columns and tables are not dynamic, they are calculated at the time of refresh.

  • amsrivastavaa's avatar
    amsrivastavaa
    Helper III
    3 years ago

    Hi Greg_Deckler ,

     

    Can we have something like this using DAX as detailed below.

     

    Let say I have table Transaction as shown below

     

    ProjectTypeYearAmountTotal_Amount
    P-1A2017100160
    P-2A201750160
    P-3A201710160
    P-1A20182075
    P-2A20185575
    P-1A201975250
    P-2A201985250
    P-3A201990250

     

    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 

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      amsrivastavaa That should be possible:

      Total_Amount = CALCULATE(SUM('Transaction'[Amount]),ALLEXCEPT('Transaction','Transaction'[Type],'Transaction'[Year],'Transaction'[Project]))
      • amsrivastavaa's avatar
        amsrivastavaa
        Helper III
        3 years ago

        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