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 have the following data.
+-----+----------------+--------------+----------------+-----------------+ | id | contract_start | contract_end | contract_level | contract_reason | +-----+----------------+--------------+----------------+-----------------+ | 111 | 2020-10-01 | 2020-11-01 | 2 | start | | 112 | 2020-10-15 | 2020-11-15 | 1 | start | | 111 | 2020-12-01 | 2021-01-01 | 2 | renew | | 112 | 2020-12-15 | 2021-01-15 | 1 | renew | | 111 | 2021-02-01 | 2021-03-01 | 2 | renew | | 112 | 2021-02-15 | 2021-03-15 | 1 | renew | | 111 | 2021-04-01 | 2021-05-01 | 3 | renew | | 112 | 2021-04-15 | 2021-05-15 | 1 | renew | | 111 | 2021-06-01 | 2021-07-01 | 3 | renew | | 112 | 2021-06-15 | 2021-07-15 | 2 | renew | +-----+----------------+--------------+----------------+-----------------+
What would be the best way to get/count id of employee promoted in 2021-2021 as indicated in the contract_level field?
Shall I approach this with Power Query or DAX?
Thank you.
Solved! Go to Solution.
Hi @ikelaiah
here is the DAX formula :
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
You can try this
Promoted =
VAR _start=MINX(FILTER('Table',YEAR('Table'[Contract_start])=2021),'Table'[Contract_start])
VAR _startlevel=maxx(FILTER('Table','Table'[Contract_start]=_start),'Table'[Contract_level])
VAR _end=MAXX(FILTER('Table',YEAR('Table'[Contract_start])=2021),'Table'[Contract_start])
VAR _endlevel=maxx(FILTER('Table','Table'[Contract_start]=_end),'Table'[Contract_level])
return if(_endlevel<>_startlevel,"Yes","No")
_COUNT = COUNTAX(VALUES('Table'[ID]),[Promoted]="Yes")
pls see the attachment below
Proud to be a Super User!
Hi @ikelaiah
here is the DAX formula :
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
@aj1973 , thanks for this approach. I didn't think I could use DICTINCTCOUNT on it. So it gets me the count. Many thanks.
Many weolcomes to @ikelaiah
Please mark this thread as accepted for the rest of the community.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |