Slicer/Filter help needed

[MitieFred]

We have a series of projects, split into different operating areas of the Business and each project sits in only one group. We also indicate, on each project, which are under review or not and what stage in the project lifecycle each is at. Each project has tasks and delivery milestones.

Project Name	Business Area (x10)	Review Status (x 2)	Lifecycle Stage (x 4)
Prj 01	Credits	In Review	Kick Off
Prj 02	HR	In Review	Planning
Prj 03	Invoicing	Not Reviewed	In Progress
Prj 04	Credits	In Review	Delivery
Prj 05	HR	Not Reviewed	Kick Off
Prj 06	Invoicing	Not Reviewed	Delivery
Prj 07	Credits	In Review	In Progress
Prj 08	HR	Not Reviewed	Delivery
Prj 09	Invoicing	In Review	Planning
Prj 10	Credits	In Review	Planning
Prj 11	HR	Not Reviewed	In Progress
Prj 12	Invoicing	In Review	Kick Off

We have one further “project”, which simply contains a series of tasks relating to freeze windows throughout the year(s)

Freeze Plan

IT

Not Reviewed

In Progress

What we  want to develop is a view, where we can allow slicers to be applied based on any of the Business Areas, Review Statuses or Lifecycle Stages . . . . BUT, we also want to include the Freeze Plan in every view. The problem we face is that it is in a different Business Area, with many other IT projects, hence we don’t want to allow multiple Business Area selections as that would end up showing projects unrelated to the selected Business area. What I’m wanting to avoid is having to create a Freeze Plan for every Business Area, for every Review Status and Project Stage, potentially 80+ Freeze Plans !

I tried creating some Columns, as follows, and using them as filters against the report, but to no avail, in that it failed to show the Freeze Plan, presumably because the Slicers take precedence.

SelectedInReview = var SelectedReview=SELECTEDVALUE(Projects[InReviewAgenda])

var SelectedPrj=SELECTEDVALUE(Projects[ProjectName])

return

if(SelectedPrj="Freeze Plan",SelectedReview,Projects[InReviewAgenda])

SelectedBizArea = var SelectedBusinessArea=SELECTEDVALUE(Projects[BusinessUnit])

var SelectedPrj=SELECTEDVALUE(Projects[ProjectName])

return

if(SelectedPrj="Freeze Plan",SelectedBusinessArea,Projects[BusinessUnit])

SelectedStage = var SelectedPrjStage=SELECTEDVALUE(Projects[ProjectStage])

var SelectedPrj=SELECTEDVALUE(Projects[ProjectName])

return

if(SelectedPrj="Freeze Plan",SelectedPrjStage,Projects[ProjectStage])

Any thoughts, guidance or solutions gratefully welcomed and received

Thanks and regards

Fred

[v-frfei-msft · Accepted solution]

Hi @MitieFred ,

We can create some calculated tables and add them as slicers.

BA Slicer = VALUES('Table'[Business Area (x10)])

LC slcier = VALUES('Table'[Lifecycle Stage (x 4)])

PN slicer = VALUES('Table'[Project Name])

RS slicer = VALUES('Table'[Review Status (x 2)])

Atfer that, We can create a measure as below and put it into the  table visual.

Measure =
VAR ba =
    VALUES ( 'BA Slicer'[Business Area (x10)] )
VAR lc =
    VALUES ( 'LC slcier'[Lifecycle Stage (x 4)] )
VAR res =
    VALUES ( 'RS slicer'[Review Status (x 2)] )
RETURN
    IF (
        OR (
            MAX ( 'Table'[Project Name] ) = "Freeze Plan",
            MAX ( 'Table'[Business Area (x10)] ) IN ba
                && MAX ( 'Table'[Lifecycle Stage (x 4)] ) IN lc
                && MAX ( 'Table'[Review Status (x 2)] ) IN res
        ),
        1,
        BLANK ()
    )

[MitieFred]

@v-frfei-msft

Thanks Frank, please ignoe the above questions, as, re-reading it, I realised that the "Table" entry was the source for the slicers.  That done, it works perfectly, thankyou.

Now all I need to do is get in working with the Gantt PBI report.

Regards

Fred

[MitieFred]

Hi @v-frfei-msft (Frank)

Just noticed, while reviewing your freeze.pbix, that the Review Status seems to stop the Freeze Plan showing, in some instances, if you have it set to "In Review".

e.g. for HR or Invoicing, where there are both In and Not reviewed entries

Thanks for any help you can give

Fred

[MitieFred]

Hi @v-frfei-msft , thanks for the solution, I've managed to get most of the way through but one step is causing me problems, namely, how did you create the "Table" with the Business Area, Lifecycle Stage, Project Name and Review Status in.  I understand the Measure, but the actual creation of "Table" has me stuck.

Thanks again

Fred