Forum Discussion
How to get the Status Column in Power Bi
Hello Super Users ,
Need help on the below scenario.
So i have 3 tables -
Employee Assigned Level
| Employee Name | Levels |
| John | Level 1 |
| Peter | Level 2 |
| Jane | Level 3 |
| Tina | Level 4 |
| Joe | Level 1 |
| Mike | Level 2 |
| Larry | Level 3 |
| Anita | Level 4 |
| Nimi | Level 1 |
| Shane | Level 2 |
| Scott | Level 3 |
Level Master
| Transaction Code | Transaction name | Assigned Level |
| 12 | ABC | Level 1 |
| 13 | DEF | Level 1 |
| 14 | GHI | Level 1 |
| 15 | JKL | Level 2 |
| 16 | MNQ | Level 2 |
| 17 | PTS | Level 2 |
| 18 | MNS | Level 3 |
| 19 | YWX | Level 3 |
| 20 | UFO | Level 3 |
| 21 | JKL | Level 4 |
| 22 | PQR | Level 4 |
| 23 | TAT | Level 4 |
| 26 | PPP | Level 5 |
| 27 | ABB | Level 6 |
| 28 | IRS | Level 5 |
Base Data
| Employee Name | Transaction Name | Transaction Status (Yes/No) |
| John | ABC | ? |
| John | DEF | |
| John | GHI | |
| Peter | MNQ | |
| Peter | JKL | |
| Peter | PTS | |
| Jane | MNS | |
| Tina | JKL | |
| Tina | PQR |
First table represents employee name with the level assigned to them to complete. Second table is a level master wherein it gives details of the what the level comprises of. So to complete a particular level, employee has to complete the related transactions.
The third table is the actual base data.
Here is what is needed-
-> I need a status column wherein I want to know if a employee completes all the transactions in a level then should be marked Yes, otherwise should be No
Issue here is needless of what is assigned the employee will complete any level, we want to capture the transaction status, also the number of transaction(Count) he completed within the level assigned to them
I could do this in an excel using concatenate and lookup, need help in figuring out how to do this in Power BI.
I have been stuck on this for a while now. Your inputs would really help.
Thanks in Advance
Anonymous take a look here
4 Replies
smpa01Community Champion
Anonymous a calculated column would be following
Column = VAR _alreadyCompleted = CALCULATE ( DISTINCTCOUNT ( Actual[Transaction Name] ), ALLEXCEPT ( Actual, Actual[Employee Name] ) ) VAR _supposedToComplete = CALCULATE ( CALCULATE ( COUNTX ( FILTER ( dimLevel, dimLevel[Assigned Level] = CALCULATE ( MAX ( dimEmployee[Levels] ), CROSSFILTER ( Actual[Employee Name], dimEmployee[Employee Name], BOTH ) ) ), dimLevel[Transaction name] ) ), ALLEXCEPT ( Actual, Actual[Employee Name] ) ) VAR _x = IF ( _alreadyCompleted = _supposedToComplete, "Yes", "No" ) RETURN _xand you can use the same expression in a measure as well
Thank You smpa01 for your quick response.
This works beautifully, But in the same table if i want to know the number of assigned transactions an employee completed v/s the actual Transaction he is supposed to complete.
How should i calculate that as employee has done many transaction which may or may not be assigned to his level.
Thanks again
- smpa01
Anonymous take a look here