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.
Hello All,
In my table i have the timelog details for each empployees.
So i am trying to calculate the count of days that each employee not entered the timelog i.e the blank rows.
By using below formula as a measure i got right the values.
EmpCount = CALCULATE(COUNTROWS(Timelog),FILTER(Timelog,Timelog[Time Log]=BLANK()))
As you can see in above image, that there are three slicers(Date, Emp Name & SBU) which will be using as filters for the same.
And by measure it is filtering the table values perfectly.
Now I am trying get these emp count values into some ranges like 0-5, 5-10, 10-15,>15.
I did it in measure using switch function. but i cant use this switch measure in column bar chart's AXIS field as it needs only a column.
So, now how can i write a calculated column which can give me the same result as the above measure is giving so that i can write a ranges calculated column further.
Any help.
Mohan V
Solved! Go to Solution.
Hi @Anonymous,
Now I am trying get these emp count values into some ranges like 0-5, 5-10, 10-15,>15.
I did it in measure using switch function. but i cant use this switch measure in column bar chart's AXIS field as it needs only a column.
So, now how can i write a calculated column which can give me the same result as the above measure is giving so that i can write a ranges calculated column further.
Please modify the measure for [EmpCount] as below:
EmpCount = CALCULATE ( COUNTROWS ( Timelog ), FILTER ( ALLEXCEPT ( Timelog, Timelog[Emp Name] ), Timelog[Time Log] = BLANK () ) )
_Column = IF ( [EmpCount] < 5, "0-5", IF ( [EmpCount] >= 5 && [EmpCount] < 10, "5-10", IF ( [EmpCount] >= 10 && [EmpCount] < 15, "10-15", ">15" ) ) )
Best regards,
Yuliana Gu
Hi @Anonymous,
Now I am trying get these emp count values into some ranges like 0-5, 5-10, 10-15,>15.
I did it in measure using switch function. but i cant use this switch measure in column bar chart's AXIS field as it needs only a column.
So, now how can i write a calculated column which can give me the same result as the above measure is giving so that i can write a ranges calculated column further.
Please modify the measure for [EmpCount] as below:
EmpCount = CALCULATE ( COUNTROWS ( Timelog ), FILTER ( ALLEXCEPT ( Timelog, Timelog[Emp Name] ), Timelog[Time Log] = BLANK () ) )
_Column = IF ( [EmpCount] < 5, "0-5", IF ( [EmpCount] >= 5 && [EmpCount] < 10, "5-10", IF ( [EmpCount] >= 10 && [EmpCount] < 15, "10-15", ">15" ) ) )
Best regards,
Yuliana Gu
Hi @Anonymous
Maybe you can try an If column?
Something like :
IF(EmpCount<5;"0-5";IF(EmpCount<10;"5-10";IF(EmpCount<15;"10-15";">15")))
- Quentin
@quentin_vigne Thanks for the reply.
I have already tried that, but im getting wrong output as in below image.
Calulated column formula
_Column = IF([EmpCount]<5,"0-5",IF([EmpCount]>=5 && [EmpCount]<10 ,"5-10",IF([EmpCount]>=10 && [EmpCount]<15,"10-15",">15")))
In measure it is givng perfect values.
_Measure= IF([EmpCount]<5,"0-5",IF([EmpCount]>=5 && [EmpCount]<10 ,"5-10",IF([EmpCount]>=10 && [EmpCount]<15,"10-15",">15")))
But as i said i cant use this measure in bar chart axis feild..
Any suggestions Please.
Mohan V
@Anonymous
Can you copy and paste this one :
New_Column = IF([EmpCount]<5,"0-5",IF([EmpCount]<10 ,"5-10",IF([EmpCount]<15,"10-15",">15")))
You don't need the && condition because If goes from one level to another.
If it is still not working, is [EmpCount] the SAME thing that "Count of Emp No" on your snapshot ?
- Quentin
Still no luck.
New_Column = IF([EmpCount]<5,"0-5",IF([EmpCount]<10 ,"5-10",IF([EmpCount]<15,"10-15",">15")))
getting the same Output.
If it is still not working, is [EmpCount] the SAME thing that "Count of Emp No" on your snapshot ?
Yes they are.
Any Help.
Mohan V
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |