Measure for Filtering on different criteria within the table

[yogeshk77]

I have a Tasks table with this data

And I need to show data in such way with filters

Notice that I don't want to show Release Areas in the filters on the report screen

Now, I need a measure that when we select Area, it should show Hrs of the person for the respective related Release Area (for e.g. Release\Area 1 in the above shown example)

I have written a measure, but it's showing hrs for all records, instead of only one , like this below -

Measure is like this - 

Here is the Power BI File

[johnt75]

Try

Release Hrs =
VAR AreaFilter =
	TREATAS(
		SELECTCOLUMNS(
			VALUES( 'Area Master'[Area Path] ),
			"@release", "Release\" & 'Area Master'[Area Path]
		),
		'Area Master'[Area Path]
	)
VAR IterationFilter =
	VALUES( 'Iteration Master'[Iteration Path] )
RETURN
	CALCULATE(
		SUM( 'Tasks'[Hrs] ),
		AreaFilter,
		IterationFilter
	)

[yogeshk77]

@johnt75 Sorry, I missed adding a Word "Core\" to the Area path.
When I have that, this measure is not working anymore.
Please take a look at this PBI file

[johnt75 · Accepted solution]

I would use Power Query to split out the base area as a new column on the area master table and use that on the slicer. Then amend the measure to be

Release Hrs = 
VAR AreaFilter = TREATAS( SELECTCOLUMNS( VALUES( 'Area Master'[Area] ), "@release", "Core\Release\" & 'Area Master'[Area] ), 'Area Master'[Area Path] ) 
VAR IterationFilter = VALUES( 'Iteration Master'[Iteration Path] ) 
RETURN CALCULATE( SUM( 'Tasks'[Hrs] ), AreaFilter, IterationFilter ) 

See the attached file.

[yogeshk77]

Got it.. Thanks a lot !