cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Slicer/Filter help needed

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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 ()
    )

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

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 ()
    )

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Highlighted

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

Highlighted

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

Highlighted

@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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors