Forum Discussion

amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago
Solved

Reference Table not changing

Hi Guys!!,   In Power BI Query Editor, I have created Table-A and then created Table-B as a reference table of Table-A and then apply Group by based on some column for one measure.   Now, on repo...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    I don't think that is possible because a calculated column formula does not respond to a change in slicers/filters - only measures do.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Share some data, explain the question and show the expected result.

  • amsrivastavaa's avatar
    amsrivastavaa
    Helper III
    3 years ago

    HI Ashish_Mathur ,

     

    Filters : Project

    Table-A : Columns (Project, Type,Year,Amount)

     

    ProjectTypeYearAmountTotal_Amount
    P-1Credit20171060
    P-2Credit20172060
    P-3Credit20173060
    P-1Credit2018100600
    P-2Credit2018200600
    P-3Credit2018300600

    I want to include another column say Total_Amount which can hold summataion of  column amount based on Type and Year, in above case it will be (10+20+30=60) for Type=Credit and Year =2017 And it will be (100+200+300=600) for Type=Credit and Year=2017. 

     

    To calculate, Total_Amount, I have used below DAX in Power BI DATA page

    TotalAmount = CALCULATE(SUM('Transaction'[Amount]),ALLEXCEPT('Transaction','Transaction'[Type],'Transaction'[Year]))

     

    As this TotalAmount column is created at DATA Page, so I am able to filter the data based on TotalAmount on Report Canvas, till here things are fine.

     

    Now problem comes when I have UN-selected P-1 in Project filter in Report canvas, then I will have data like this as shown below 

     

    ProjectTypeYearAmountTotal_Amount
    P-2Credit20172060
    P-3Credit20173060
    P-2Credit2018200600
    P-3Credit2018300600

     

    i.e. P-1 data is not available now, that perfect.

    And, in this case my TotalAmount which I have calculated via DAX at Data page is still showing Total that includes P-1 data as well, which is not correct.

     

    I want, TotalAmount will always be calculated based on data avaialble after filterationas shown below

     

    ProjectTypeYearAmountTotal_Amount
    P-2Credit20172050
    P-3Credit20173050
    P-2Credit2018200500
    P-3Credit2018300500

     

    I want, this too be done at table level itsels so that I can use this table further as well, if i will use any measure filteration concpets at Report Canvas level, I will not be able to use that data any further.

     

    Please suggest!!''

     

    Thanks 

    Amit 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      3 years ago

      Hi,

      These measures work.

      Amt = SUM(Data[Amount])
      Measure = CALCULATE([Amt],ALLSELECTED(Data[Project]))

      Hope this helps.

      • amsrivastavaa's avatar
        amsrivastavaa
        Helper III
        3 years ago

        Hi Ashish_Mathur ,

         

        I want to add this column at DATA page, as I want to use this data some where else.

         

         

        1. While using provided DAX, its not giving me summation based on what Project left, instead it giving me individual amount of Measure.

         

        FIrst, I need to implement here at Data page.

        Secondly, when user select let say P-2 and P-3 then summation would be of AMOUNT for P-2 and P-3 only.

         

        Please suggest 

         

        Thanks

        Amit 

    • v-zhangti's avatar
      v-zhangti
      Community Support
      3 years ago

      Hi, amsrivastavaa 

       

      You can try the following methods.
      Measure:

      Total_Amount = CALCULATE(SUM('Transaction'[Amount]),ALLSELECTED('Transaction'[Project]))

      Data view:

      The calculated columns of the Data view are fixed. Cannot be changed by slicers or filters. 

       

      This is by design. Power BI is now unable to achieve what you need . You can submit an idea for it at   https://ideas.powerbi.com/ideas/   and wait for users with the same needs as you to vote for you to help make it happen as soon as possible.

       

      Best Regards,

      Community Support Team _Charlotte

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.