Struggling with disconnected slicer and ALLSELECTED

[masplin]

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

[amitchandak]

@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.

[masplin]

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

[masplin]

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

[masplin · Accepted solution]

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