cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WilkerFRL94
Frequent Visitor

Filter context not passed into calculated column

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:

WilkerFRL94_2-1624307279537.png

 

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.

 

WilkerFRL94_4-1624307482194.png

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:

WilkerFRL94_5-1624307515371.png

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.

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors