Reference Table not changing

[amsrivastavaa]

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 

[Ashish_Mathur]

Hi,

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

[amsrivastavaa]

HI @Ashish_Mathur ,

Filters : Project

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

Project	Type	Year	Amount	Total_Amount
P-1	Credit	2017	10	60
P-2	Credit	2017	20	60
P-3	Credit	2017	30	60
P-1	Credit	2018	100	600
P-2	Credit	2018	200	600
P-3	Credit	2018	300	600

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 

Project	Type	Year	Amount	Total_Amount
P-2	Credit	2017	20	60
P-3	Credit	2017	30	60
P-2	Credit	2018	200	600
P-3	Credit	2018	300	600

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

Project	Type	Year	Amount	Total_Amount
P-2	Credit	2017	20	50
P-3	Credit	2017	30	50
P-2	Credit	2018	200	500
P-3	Credit	2018	300	500

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 

[v-zhangti]

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.

[Ashish_Mathur]

Hi,

These measures work.

Amt = SUM(Data[Amount])

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

Hope this helps.

[amsrivastavaa]

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 

[Ashish_Mathur · Accepted solution]

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

[aj1973]

Hi @amsrivastavaa 
Are the 2 tables joined?

[amsrivastavaa]

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