cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Struggling with disconnected slicer and ALLSELECTED

I'm sure this is easy, but just cant work it out. 

 

Before COVID we did performace assessment by picking a user and comparing certian metrics to to all the other users doing the same role as every had similar work loads.

 

 

MIN Calls per Hour In/ User UserJob Peer = 
            CALCULATE(  
                       [MIN Calls per Hour In/ User],
                       EXCEPT(
                            ALL(AdviserRole[Name]), 
                            ALLSELECTED(AdviserRole[Name])
                                )
                    )

 

 

This coded worked fine as pick the user from the Adviserrole dropdown and calcuates by getting all users and excluding the one you choose.  

 

I have now been asked to allow the removal of certian users from the peer group as some peopel are working in different ways.  i though the solution was to create a duplicate of the AdviseRole table (PeerAdvisers) and have this as a second name slicer. Then somehow use ALLSELECTED(PeerAdvisers[Names]) to filter the ALL(Advisers[Name]) table as well. I created an inactive relationship between AdvisdeRole and PeerAdviser as assume I need to somehow take the names selected on PeerAdvisers and filter out the related names on the AdviserRole as that is the table that drives all the metrics.

 

My DAX grammar is not very good and work things out by bute force so hoping someone can help me with how to use a disconnected list of names to filter the main list of names? Plus preferably also exclude the name chose in AdviserRole[Name]

 

Afraid the model is enromous so no idea how to even post an example. 

 

Thanks for any advice

Mike

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Post Prodigy
Post Prodigy

Re: Struggling with disconnected slicer and ALLSELECTED

this works

 

MIN Calls per Hour In/ User UserJob Peer = 
VAR
Peers=CALCULATE(COUNTROWS(ALLSELECTED(PeerAdvisers[Name])))
RETURN
            CALCULATE(  
                       [MIN Calls per Hour In/ User],
                       FILTER(
                            EXCEPT(
                                    ALL(AdviserRole[Name]), 
                                    ALLSELECTED(AdviserRole[Name])),
                            IF(
                                Peers<=10,
                                NOT(AdviserRole[Name] IN ALLSELECTED(PeerAdvisers[Name])),
                                AdviserRole[Name] IN ALLSELECTED(PeerAdvisers[Name])
                                )
                              )
                            )
                    

. Never used NOT IN before so very helpful thanks 

View solution in original post

4 REPLIES 4
Highlighted
Super User IX
Super User IX

Re: Struggling with disconnected slicer and ALLSELECTED

@masplin , What i got if from independent slicer you have take name and use a not in

something like this

MIN Calls per Hour In/ User UserJob Peer =

CALCULATE(
[MIN Calls per Hour In/ User],
filter(AdviserRole , not( AdviserRole[peer name] in allselected(AdviserRole_slicer[peer name])))
EXCEPT(
AdviserRole[Name],
ALLSELECTED(AdviserRole[Name])
)
)

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Post Prodigy
Post Prodigy

Re: Struggling with disconnected slicer and ALLSELECTED

I had to switch this around to get it working. 

 

MIN Calls per Hour In/ User UserJob Peer = 
            CALCULATE(  
                       [MIN Calls per Hour In/ User],
                       FILTER(
                            EXCEPT(
                                    ALL(AdviserRole[Name]), 
                                    ALLSELECTED(AdviserRole[Name])),
                                    NOT(AdviserRole[Name] IN ALLSELECTED(PeerAdvisers[Name]))
                            )
                    )
                    

However problme now is if you dont select anything in the PeerAdviser[Name] slicer it defaults to All so removes every name and returns a blank.  There doesnt seem to be anyway to make the slicer retrun null if you dont pick something. So is there a way to make ALLSELECTED (Peeradvisers[Name] retrun a null if there number of slected names is less than 1?

 

Thanks for your help

Mike

Highlighted
Post Prodigy
Post Prodigy

Re: Struggling with disconnected slicer and ALLSELECTED

I tried this but doenst work despite peers=321 if nothing selected

 

MIN Calls per Hour In/ User UserJob Peer = 
VAR
Peers=CALCULATE(COUNTROWS(ALLSELECTED(PeerAdvisers[Name])))
RETURN
            CALCULATE(  
                       [MIN Calls per Hour In/ User],
                       FILTER(
                            EXCEPT(
                                    ALL(AdviserRole[Name]), 
                                    ALLSELECTED(AdviserRole[Name])),
                            IF(
                                Peers<=10,
                                NOT(AdviserRole[Name] IN ALLSELECTED(PeerAdvisers[Name])),
                                BLANK()
                                )
                              )
                            )
Highlighted
Post Prodigy
Post Prodigy

Re: Struggling with disconnected slicer and ALLSELECTED

this works

 

MIN Calls per Hour In/ User UserJob Peer = 
VAR
Peers=CALCULATE(COUNTROWS(ALLSELECTED(PeerAdvisers[Name])))
RETURN
            CALCULATE(  
                       [MIN Calls per Hour In/ User],
                       FILTER(
                            EXCEPT(
                                    ALL(AdviserRole[Name]), 
                                    ALLSELECTED(AdviserRole[Name])),
                            IF(
                                Peers<=10,
                                NOT(AdviserRole[Name] IN ALLSELECTED(PeerAdvisers[Name])),
                                AdviserRole[Name] IN ALLSELECTED(PeerAdvisers[Name])
                                )
                              )
                            )
                    

. Never used NOT IN before so very helpful thanks 

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors