topic Compare strings in previous month in DAX Commands and Tips

Compare strings in previous month

Anonymous — Thu, 19 Nov 2020 19:15:56 GMT

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

Re: Compare strings in previous month

MattAllington — Thu, 19 Nov 2020 19:54:25 GMT

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")

Re: Compare strings in previous month

Anonymous — Thu, 19 Nov 2020 19:57:38 GMT

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.

Re: Compare strings in previous month

amitchandak — Fri, 20 Nov 2020 03:35:20 GMT

Anonymous , Try a measure like

calculate( count(Table[Promotion]) , filter(Table, Table[Promotion] = "Promotion" && Table[ Managerial Level] in { "Staff" ,"Manager"}))

calculate( count(Table[Promotion]) , filter(Table, Table[Promotion] = "Promotion" && Table[ Managerial Level] in { "Staff" }))

Re: Compare strings in previous month

Payeras_BI — Fri, 20 Nov 2020 15:46:29 GMT

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" ) )