topic Measure for Filtering on different criteria within the table in DAX Commands and Tips

Measure for Filtering on different criteria within the table

yogeshk77 — Tue, 20 Dec 2022 10:24:45 GMT

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

Re: Measure for Filtering on different criteria within the table

johnt75 — Tue, 20 Dec 2022 12:15:56 GMT

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 )

Re: Measure for Filtering on different criteria within the table

yogeshk77 — Wed, 21 Dec 2022 04:23:01 GMT

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

Re: Measure for Filtering on different criteria within the table

johnt75 — Wed, 21 Dec 2022 13:55:37 GMT

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.

Re: Measure for Filtering on different criteria within the table

yogeshk77 — Thu, 22 Dec 2022 06:20:29 GMT

Got it.. Thanks a lot !