Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Convert Measure Formula into Calculated Column

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

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

5 Replies

  • quentin_vigne's avatar
    quentin_vigne
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        quentin_vigne
        Icon for Solution Sage rankSolution Sage
        8 years ago

        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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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