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 report canvas, I have created one filter F-1 (derived from Table-A) and placed Table-A in table visualization and TableB in onother visualization.

 

Now, when i selected any filter in F-1, it only filtered Table-A, wondering why Table-B is not getting filtered even though it is reference by Table-A.

 

Please suggest how this is possible.

 

Thanks

Amit 

 

 

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.

8 Replies

    • amsrivastavaa's avatar
      amsrivastavaa
      Helper III
      3 years ago

      Hi aj1973 ,

       

      As in Table -B, I have used Grouped by so after that there is no Legitimate column available in Table -B on which joins will be apply.

       

      Any other work around, please suggest

       

      Thanks Amit

       

       

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