Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have the following information: Employee Id, Salary, salary rank:
Emp.Id | Salary | Salary rank |
100 | 1000 | 50% |
101 | 1200 | 74% |
102 | 1300 | 80% |
103 | 1350 | 82% |
104 | 1500 | 91% |
I would like to present employees information for several ranges of salary ranks:
0-50%
510-75%
75-85%
85% and more
1. To present on visualization chart:
Axis x - ranges of salary ranks
Axis y - number of emplyees
2. Each time I press on shart (e.g. on range 75-85%), I will see data in visualization table for only these employess.
In case of 75-85% I will get data only for 2 employess:
Emp.Id | Salary | Salary rank |
102 | 1300 | 80% |
103 | 1350 | 82% |
I created a range table with MIN and MAX + some measures.
The main one is:
Count Rating =
IF(NOT(ISBLANK('Rating'[Rating_Salary])),
CALCULATE(
COUNT('compare to survey'[EmpId),
FILTER('compare to survey', 'compare to survey'[rate]
< 'Rating'[MaxThreshold_rating]), FILTER('compare to survey', 'compare to survey'[rate] >= 'Rating'[MaxThreshold_rating])))
However, I receive information for all employess as following:
Emp.Id | Salary | Count Rating |
100 | 1000 | |
101 | 1200 | |
102 | 1300 | 1 |
103 | 1350 | 1 |
104 | 1500 |
How can I fix it?
Hi @gilr1975 ,
I create a sample. Please check if it is what you want.
Measure =
CALCULATE (
COUNT ( 'Table'[Emp.Id] ),
FILTER (
VALUES ( 'Table'[Salary rank] ),
COUNTROWS (
FILTER (
Rating,
'Table'[Salary rank] >= Rating[MinThreshold_rating]
&& 'Table'[Salary rank] <= Rating[MaxThreshold_rating]
)
)
)
)
Reference:
https://www.daxpatterns.com/dynamic-segmentation/
You could download my sample to have a try.
Thank you very much.
You're welcome. I'm glad it works for you.😊
@gilr1975 , Not very clear to me.
Please check if these can help
https://www.daxpatterns.com/static-segmentation/
https://www.daxpatterns.com/dynamic-segmentation/
Thanks a lot!
It helped me. It is exactly what I looked for.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |