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 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 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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.

     

     

    • amsrivastavaa's avatar
      amsrivastavaa
      Helper III
      3 years ago

      Hi Greg_Deckler ,

       

      Reason, why I want to have it as a column is

      - I want to filter transaction table based on this column.

      - Whatever data left after applying filter, I have to create Matrix visual for that data.

       

      So, if I will create measure for this,  I will not be able to filter the data and subsequently not able to create Matrix visual.

       

      ANy thought or work around??

       

      Thanks

      Amit

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        amsrivastavaa You can filter a visual using a measure. I can't exactly picture what you are trying to do here in terms of your matrix. Can you provide a mock-up? 

         

        The only way to do this using calculated columns is you would have to create a column for every possible combination of what users might select/filter and that's just not a feasible solution. I'm pretty certain there is a way to get what you want using measures, but I need to understand what you are really trying to accomplish because it is not clear to me.