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

      • amsrivastavaa's avatar
        amsrivastavaa
        Helper III
        3 years ago

        Hi Greg_Deckler 

         

        I have placed the PBI on onedrive, please find on the below URL

         

        https://1drv.ms/u/s!Ahtm7otFIxr8cX5JDN5SEc0Vajw?e=xREfsH

         

        As a brief, below are the requirement detail

         

        Requirement

        User wants to see Matrix report only for those project where AMOUNT/Total AMOUNT is less than 25% (if user selected <25% in Range slicer).

         

        Other details are available in PBI.

         

        Thanks

        Amit