How to get the Status Column in Power Bi

[Anonymous]

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]

Please help on this 

[Anonymous]

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 · Accepted solution]

@Anonymous  take a look here

[smpa01]

@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
    _x

and you can use the same expression in a measure as well