Chandeep Ji thanks for your all videos, especially dax challenges, I idid this challenge by following steps 1. Create Index column in power query 2. Create a calculate column as 'Previous value = CALCULATE(MAX(Data[Role]), FILTER(Data, Data[Index]=EARLIER(Data[Index])-1))' 3. Create another calculate column as 'Stint Count = CALCULATE(COUNT(Data[Index]),IF(Data[Role] Data[Previous value],1))'
Hi Chandeep, First I added an index column to the table. Next I added a calculated column named [Stints]. Stints = IF( Data[Role]=LOOKUPVALUE(Data[Role],Data[Index],Data[Index]-1), 0, 1 ) I can then put EmpCode and Stints in a matrix and Sum the values in the Stints column. This gives me the exact number of stints. Regards, Ytzen
@@nithinkumar6734 the Data[Index] contains the row number . Data[Index]-1 selects the previous row. If the present role (stint) is different from the previous stint then it’s a new stint so therefore I count 1.
Hi Chandeep, Thanks a lot for your perfect Powerbi tutorials. I think one of the solutions for solving this challenge is to create a measure with this formula: NumberOfEmployeeRoles = SUMX(SUMMARIZE(data,Data[EmpCode],Data[Role],"Role Distinct Count",DISTINCTCOUNT(Data[Role])),[Role Distinct Count]) It works for me. Please give feedback.
I had this interesting question. How can we create self join for a table after applying filters... Synario : I have a customers data base who have start date and end date based on agreement. Some arguments will valid for 1 years. Some arguments will valid for more than a year.... Q1) in each month or quarter how many customers are expiring( based on end date will check how many expiring).. Q1) how many partners are came after they got expired (start date should be after the end date of expired one)....
Hi I am new to power bi don't know if this is what u said but I tried: stints = CALCULATE( DISTINCTCOUNT(Data[Role])) Can someone say if I am right or wrong?
That's a good thought, and would work if each employee could only do a role one time. However if you look at employee 105782 for example, they jump back and forth between Branch Manager and Team Supervisor. Doing a distinct count of roles by employee misses their second stint in each of those roles, and returns a value of 3 rather than the correct value of 5.
Chandeep Ji thanks for your all videos, especially dax challenges, I idid this challenge by following steps
1. Create Index column in power query
2. Create a calculate column as 'Previous value = CALCULATE(MAX(Data[Role]), FILTER(Data, Data[Index]=EARLIER(Data[Index])-1))'
3. Create another calculate column as 'Stint Count = CALCULATE(COUNT(Data[Index]),IF(Data[Role] Data[Previous value],1))'
Hi Chandeep. Great challenge! Will have to think about this one.. looking forward to the solutions. Thumbs up!!
Hi Chandeep,
First I added an index column to the table.
Next I added a calculated column named [Stints].
Stints =
IF(
Data[Role]=LOOKUPVALUE(Data[Role],Data[Index],Data[Index]-1),
0,
1
)
I can then put EmpCode and Stints in a matrix and Sum the values in the Stints column.
This gives me the exact number of stints.
Regards,
Ytzen
why there is Data[index]-1 in the above formula can you explain?
@@nithinkumar6734 the Data[Index] contains the row number . Data[Index]-1 selects the previous row. If the present role (stint) is different from the previous stint then it’s a new stint so therefore I count 1.
@@terpstry so whenever there is a role change its return 1 right . Great logic
@@nithinkumar6734 That’s right. This approach produces the right number of stints.
If we did like this see the Employee
id EMPC 616 It comes 0 but in Data he's a Regional manager
Number of roles = COUNTROWS(DISTINCT(Data[Role]))
Hi Chandeep,
Thanks a lot for your perfect Powerbi tutorials.
I think one of the solutions for solving this challenge is to create a measure with this formula:
NumberOfEmployeeRoles = SUMX(SUMMARIZE(data,Data[EmpCode],Data[Role],"Role Distinct Count",DISTINCTCOUNT(Data[Role])),[Role Distinct Count])
It works for me. Please give feedback.
Chandeep, Thanks a million
Glad you like it!
Hi chandeep. I had a similar case where I need to find the renewals.
I had this interesting question. How can we create self join for a table after applying filters...
Synario :
I have a customers data base who have start date and end date based on agreement. Some arguments will valid for 1 years. Some arguments will valid for more than a year....
Q1) in each month or quarter how many customers are expiring( based on end date will check how many expiring)..
Q1) how many partners are came after they got expired (start date should be after the end date of expired one)....
Countrows(filter(employee stints, earlier(employeestints[Role]=employeestints[Role])))
I think this might work...
If this is a measure.. the earlier won't work this way.
@@GoodlyChandeep okay... Tq
Hi I am new to power bi don't know if this is what u said but I tried:
stints = CALCULATE( DISTINCTCOUNT(Data[Role]))
Can someone say if I am right or wrong?
That's a good thought, and would work if each employee could only do a role one time. However if you look at employee 105782 for example, they jump back and forth between Branch Manager and Team Supervisor. Doing a distinct count of roles by employee misses their second stint in each of those roles, and returns a value of 3 rather than the correct value of 5.
@@brianjulius6401 Thanks for the response man. Trying to find a solution with the knowledge I have.
hello chandeep can it be solved only using dax or we can use power query
Summarize then count, easier