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.
Hi,
I am stuck on this problem from last 2 days; have done it on excel, solved it mathematically on notepad but power bi continues to elude me.
Here is what i want to do:
I have a skills filter with around 400 skills. I have data in table with people and their related skills. Something like given below:
Person | Skill |
A | X |
B | Y |
C | Z |
A | Y |
A | Z |
B | X |
D | Z |
E | X |
F | Y |
F | Z |
D | X |
Now, when i select X & Y in the skill slicer, i want a person measure to give a value of 2 because only A & B have both the skills, instead it is giving me 5 by filtering on distinct values on person column who have either X or Y.
Solved! Go to Solution.
Hi @Anonymous
Try this measure in a card visual:
Measure = VAR NumSlicedSkills_ = COUNTROWS ( DISTINCT ( Table1[Skill] ) ) RETURN SUMX ( DISTINCT ( Table1[Person] ), INT ( CALCULATE ( COUNT ( Table1[Skill] ) ) = NumSlicedSkills_ ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi @Anonymous
Try this measure in a card visual:
Measure = VAR NumSlicedSkills_ = COUNTROWS ( DISTINCT ( Table1[Skill] ) ) RETURN SUMX ( DISTINCT ( Table1[Person] ), INT ( CALCULATE ( COUNT ( Table1[Skill] ) ) = NumSlicedSkills_ ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hey,
It did not work 😞 It just returned all rows as per slicer.
There is one more small issue. Using your measure, i created this final measure:
Replace "" with BLANK()
I guess I would have to see the data model (or pbix) to be able to know what might be going on with that "distortion"
Cheers
Sure. Thank you. That Blank() part worked amazingly! Cant really share the database; however, will try to explain it as best as i can:
3 databases:
Person-Skill
Task-Skill
Skill
Skill -> Merge of skills from rest of the 2 databases
Person-Skill |
A |
B |
Task-Skill |
B |
C |
Skill database |
A |
B |
C |
Selected skills | Person-Skill - 67 rows | Task-Skill- 23 rows |
A | 3 | 0 |
B | 64 | 22 |
C | 0 | 1 |
Desired result: | 0 | 0 |
Current result: | 3 | 0 |
This is one.
When i said it gets distorted, i meant the following:
Skill database |
A |
B |
C |
D |
Person-Skill |
A |
B |
D |
Task-Skill |
B |
C |
D |
Detail Skill Set | Level | Person-Skill - 113 rows |
A | Level 1 | 1 |
A | Level 2 | 21 |
A | Level 3 | 37 |
A | Level 4 | 5 |
B | Level 2 | 1 |
B | Level 3 | 2 |
D | Level 1 | 1 |
D | Level 2 | 11 |
D | Level 3 | 31 |
D | Level 4 | 3 |
Must have Skills | Level | Task-Skill - 79 rows |
A | Level 1 | 1 |
A | Level 2 | 8 |
A | Level 3 | 11 |
A | Level 4 | 2 |
C | Level 2 | 1 |
D | Level 1 | 2 |
D | Level 2 | 14 |
D | Level 3 | 32 |
D | Level 4 | 8 |
First graph - Level wise | Person-Skill | Level 2 | Level 3 | Level 4 |
Current result | 4 | 1 | 2 | 1 |
Second graph - Overall | Person-Skill |
Current result | 3 |
Person-Skill | Task-Skill | |
Desired result | 0 | 0 |
Does it make sense?
Hi,
Does anyone have a solution for this?
Oh yes! i was mistaken. I gave the incorrect column name from my dataset of 100 columns. You are a genius. 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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |