Convert Measure Formula into Calculated Column

[Anonymous]

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

* Intentionally Emp Names are colored as white

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

[v-yulgu-msft · Accepted 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

[quentin_vigne]

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

[Anonymous]

@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

[quentin_vigne]

@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

[Anonymous]

@quentin_vigne

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