Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello. I need a DAX Measure to count how many staff employees got promoted into a leadership position. This is what my data looks like. So it would need to return 1 because 101 was staff. 102's promotion doesnt count because he was already in a leadership position.
ID | Date | Managerial Level | Promotion |
101 | 10/20 | Staff | |
102 | 10/20 | Manager | |
103 | 10/20 | Staff | |
104 | 10/20 | Director | |
101 | 11/20 | Manager | Promotion |
102 | 11/20 | Executive Manager | Promotion |
103 | 11/20 | Staff | |
104 | 11/20 | Director |
Thanks
Hello @Anonymous,
Try with this measure:
Count of Promotions from Staff =
COUNTROWS (
FILTER (
ADDCOLUMNS (
'Table',
"Previous managerial level",
VAR __id = 'Table'[ID]
VAR __date = 'Table'[Date]
RETURN
MAXX (
TOPN (
1,
FILTER (
ALL ( 'Table' ),
'Table'[ID] = __id
&& 'Table'[Date] < __date
),
'Table'[Date], DESC
),
'Table'[Managerial Level]
)
),
[Previous managerial level] = "STAFF"
&& 'Table'[Promotion] = "Promotion"
)
)
@Anonymous , Try a measure like
calculate( count(Table[Promotion]) , filter(Table, Table[Promotion] = "Promotion" && Table[ Managerial Level] in { "Staff" ,"Manager"}))
or
calculate( count(Table[Promotion]) , filter(Table, Table[Promotion] = "Promotion" && Table[ Managerial Level] in { "Staff" }))
@MattAllington Hi. I don't have access to the data source. I can't add new columns. I want to try to solve this using DAX to avoid having to request a change in the database.
Power BI is easy when you prep your data. I would add a conditional column using Power Query to indicate if the Mgr level is considered to be a staff promotion. Then you can write a simple DAX formula
CALCULATE(COUNTROWS(Table),table[promotion]="Promotion",table[new column]="from staff")
User | Count |
---|---|
47 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
57 | |
29 | |
20 | |
16 |