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

Filtering based on selectedvalue

Hi guys,

 

Hoping someone can help me out, i have been looking at this for a couple of days now and really struggling to figure out a solution

 

Within my model I have a 'Task' table that contains details such as start date, end date and effort required to complete each task. I have created the ability to draw an S-curve (line chart) from my 'Dates' table to show when each task is planned over time using an 'Effort per day' calculated column that is the summed in the visualisation. 

 

I now need to create a way of filtering my 'Dates'[Effort required] column by the 'Task'[Code4] column, which contains details of who is doing the work, so that the S-Curve reflects only the data relating to the selected company.

 

Below is my 'Dates'[Effort required] calculated column. The line which is currently filtered out creates exactly the result I require but involves a hard coded string being passed to the calulation. I was hoping the preceeding line would give the same result by passing a selected value from a slicer that is created from distinct 'Task'[Code4] values.

 

However this is simply returning all 'Effort per day' values and is not filtering the data down as desired. Any ideas what is going wrong??

 

Effort Required = if(Dates[WorkDays]=false,0,
    calculate(
    sum('Task'[Effort per Day]),
   		filter('Task', Dates[Date]>='Task'[Planned Effort Start Date]-1),
    	        filter('Task',Dates[Date]<='Task'[Planned Effort Finish Date]),
		filter(task,Task[Code4]=[selected value]),
		//filter(task,Task[Code4]="Construction"),	
		filter(ReportDateSummary,ReportDateSummary[ReportDateCounter]=1))
		)
2 REPLIES 2
Moderator v-sihou-msft
Moderator

Re: Filtering based on selectedvalue

@DCope

 

In this scenario, since it can return correct result when you hard code the 'Task'[Code4] value, your calculation can take the filter properly.

 

Just remove "filter(task,Task[Code4]=[selected value])" , then select item in slicer to see if it can return correct result. If not, please replace it with "ALLSELECTED(Task[Code4])".

 

If it still doesn't work, please directly put your [selected value] with Date table entries to check the result on row level. 

 

Regards,

DCope Frequent Visitor
Frequent Visitor

Re: Filtering based on selectedvalue

Unfortunately this did not work. I first removed the filter row as suggetsed and then added the ALLSELECTED(Task[Code4]) condition but as you can see from the row level data below the effort required is not changing no matter what is picked in [selected value]

 

01-11-2017 11-26-47.png01-11-2017 11-27-08.png01-11-2017 11-27-15.png

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 247 members 3,085 guests
Please welcome our newest community members: