Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Name | Hired | Left the company | Favourite color |
Jonas | 2018 | Red | |
Jeff | 2017 | 2019 | Red |
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:
Year | People who like red |
2017 | 1 |
2018 | 2 |
2019 | 2 |
2020 | 1 |
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!
Solved! Go to Solution.
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.
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.
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