topic Re: Struggling with disconnected slicer and ALLSELECTED in DAX Commands and Tips

Struggling with disconnected slicer and ALLSELECTED

masplin — Tue, 15 Sep 2020 17:28:40 GMT

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

Re: Struggling with disconnected slicer and ALLSELECTED

amitchandak — Wed, 16 Sep 2020 03:30:00 GMT

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.

Re: Struggling with disconnected slicer and ALLSELECTED

masplin — Wed, 16 Sep 2020 13:51:25 GMT

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

Re: Struggling with disconnected slicer and ALLSELECTED

masplin — Wed, 16 Sep 2020 14:03:40 GMT

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

Re: Struggling with disconnected slicer and ALLSELECTED

masplin — Wed, 16 Sep 2020 14:06:40 GMT

this works

. Never used NOT IN before so very helpful thanks