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.
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
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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |