Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@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.
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
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()
)
)
)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |