Forum Discussion

jonas123's avatar
jonas123
Regular Visitor
6 years ago
Solved

Creating a custom column from existing table

Hi!

 

I'm quite new to Power BI and Power Query and I've managed to run into a problem.

 

I am trying to visualize data about employees. In the employee table I have something like this:

NameHiredLeft the companyFavourite color
Jonas2018 Red
Jeff20172019Red

 

Then what I want to do is track the favourite color of the employees for every year. (Assuming of course that a person has the same favourite color during employment). The basic idea I have to do this is to create a new table like this:

 

YearPeople who like red
20171
20182
20192
20201

 

My question is: How do I write the M-function to create the "People who like red"-column?

Or is this the wrong way to go about visualizing this data?

 

Thank you in advance!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi jonas123 

    Create a date table

    Date = ADDCOLUMNS(CALENDAR(DATE(MIN('Table'[Hired]),1,1),TODAY()),"year",YEAR([Date]))

     

    Create a measure

    Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Name] ),
    FILTER (
        'Table',
        'Table'[Hired]
            <= MAX ( 'Date'[year] )
            && (
                'Table'[Left the company]
                    >= MAX ( 'Date'[year] )
                    || 'Table'[Left the company]
                        = BLANK ()
            )
    )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi jonas123 

    I think DAX usage would be better idea for your task

    First, create a table with years

    CalendarYearTable = GENERATESERIES(MIN('Table'[Hired]), YEAR(TODAY()))

    next, in this new table create a measure 

    People who like red = 
calculate(COUNTROWS('Table'),
FILTER(ALL('Table'),
'Table'[Favourite color]="Red" && 
'Table'[Hired]<=SELECTEDVALUE(CalendarYearTable[Value]) && 
('Table'[Left the company]>=SELECTEDVALUE(CalendarYearTable[Value]) || ISBLANK('Table'[Left the company]))
))

    see the pbix-file in attach

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi jonas123 

    Create a date table

    Date = ADDCOLUMNS(CALENDAR(DATE(MIN('Table'[Hired]),1,1),TODAY()),"year",YEAR([Date]))

     

    Create a measure

    Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Name] ),
    FILTER (
        'Table',
        'Table'[Hired]
            <= MAX ( 'Date'[year] )
            && (
                'Table'[Left the company]
                    >= MAX ( 'Date'[year] )
                    || 'Table'[Left the company]
                        = BLANK ()
            )
    )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.