Filter context not passed into calculated column

[WilkerFRL94]

I've been trying to create a calculated column that should get values from a table to another, with the source table being filtered.

So let's say that i have a source table "A" in which i have 3 columns fields: date, model and periodOfTime, where date and model are actively being filtered by slicers, and a forth one with prices. The dataset contains multiple entries for each column, so there are no unique fields for me to work with right now, so that the slicers allow me to select date and model of forecasting and get the price values for certain periods of time.

In table "B" i have a calendar in which i'm able to define periodOfTime (given in weeks) by each of the calendar days. The goal here is that for each day i can get data from a table "C" with verified data, and compare with prospected data from the table A.

As the data is actually related, if i just "drop" the price measure into my chart values, it will show correctly - for each periodOfTime (as x axis) i'll get the corresponding value of price for the selected model and date... problems are:

1) i'm unable (or at least as far as i know) to set a calculation of something like average value of a month with verified data and prospected data, since they're different columns and tables. Setting up average line on graph also doesn't works because i'll have average for each data in values fields;

So i decided to call a created column with the following expression:

verifiedAndProspected =

if(verified<>0, verified,

calculate(

AVERAGE('A'[price],

KEEPFILTERS('A'[periodOfTime] = EARLIER( 'B'[periodOfTime])

)

)

)

The verified field is the related to C price and works just fine, but the values for prospected price were not correct. So i did a little investigation and via data view i noticed that the calculation is using all of the date and model entries from A column, even though they are selected via slicer to show only one entry. The periodOfTime is right, so if i have 53 lines with dates and models on a week, if i set the formula to count it returns 53. But it seems it will just ignore the other slicers.

I've tried with/without keepfilters but i'm up to no good.

Also i don't think i can set it up with help of a measure inside the filter context for calculate (forcing date and model with SELECTEDVALUE to be filtered).

To provided some context in images:

The line with 'A'[price] comes from the table A into the chart: values are correct but i can't set verified over and make calculations on them. verifiedAndProspected should get the 'A' values but instead measure the average of multiple entries of dates and models even though they're filtered.

So if i set them into a table i see the values don't match, but values as filtered on table A can be seem below:

Nonetheless, changing model and date slicers won't affect verifiedAndProspect but do change the price when set in chart.

I'm I missing something? Is this impossible to achieve in PowerBI? Is there another solution to what i've pointed out in 1?

Thanks in advance.

[amitchandak · Accepted solution]

@WilkerFRL94 , based on what I got. If you are trying to use filter/slicer value in a new calculated column that will not work. You can not use that in a new column.

You have to create a measure in such a case

[WilkerFRL94]

When i use a measure, i can get the correct values but that's all. Now i'm stuck with a measure, which still doesn't communicate with the other data:

1) if there's verified data, i don't need the measure - and that's why it was in a if statement in first place - so it ignores the prospected data from A and;

2) get me the average values for displayed x-axis.

I do believe i can create another measure to get average of measure and verified but it will allow the user to overlap prospected and verified if the date selected is older than verified data - and that occurs on a daily basis.

EDIT: oh, i see, i needed to change my verified field to a measure and not calculated column, now i can set both measures into an if statement! It works, thank you very much!