Not able to generate Submission status column in Power BI

[Anonymous]

Hi Team,

Please help me on the below concern.

I am not able to generate Submission Status based on Check-in & Check-out columns. Kindly help me on this.

Submission Status:

If Check-in: True, Check-Out: True---> Submitted.

If Check-in: Blank, Check-Out: Blank -----> Not Submitted

If Check-in: True, Check-out: Blank ----> Partial-In

If Check-in: Blank, Check-out: True ----> Partial-out

If Mode of work: Work from Home & Work From Office on the same day, Check-out: True(2 times),Check-in:blank---Error

If Mode of work: Work from Home & Work From Office on the same day, Check-in: True(2 times)-,Check-out:blank--Error

My analysis:

I have tried this task in power query using group by along with combine functionality but in Mode of work column, we have two different values(Work from office & Work from home), Location column is having value and nulls. 

So though i have applied group by along with combine values did not get proper results.  

Note: we should not leave any other column, each and every column should be considered.

Thanks, 

Masthanaiah

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below: 

Column = 
VAR _curdept = 'Table'[Department]
VAR _curemp = 'Table'[Employee Name]
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Mode Of Work] ),
        ALLEXCEPT ( 'Table', 'Table'[Department], 'Table'[Employee Name] )
    )
VAR _wfodate =
    CALCULATE (
        MAX ( 'Table'[Working Date] ),
        FILTER (
            'Table',
            'Table'[Department] = _curdept
                && 'Table'[Employee Name] = _curemp
                && 'Table'[Mode Of Work] = "Work From Office"
        )
    )
VAR _wfhdate =
    CALCULATE (
        MAX ( 'Table'[Working Date] ),
        FILTER (
            'Table',
            'Table'[Department] = _curdept
                && 'Table'[Employee Name] = _curemp
                && 'Table'[Mode Of Work] = "Work From Home"
        )
    )
VAR _wfoci =
    CALCULATE (
        MAX ( 'Table'[Check-in] ),
        FILTER (
            'Table',
            'Table'[Department] = _curdept
                && 'Table'[Employee Name] = _curemp
                && 'Table'[Mode Of Work] = "Work From Office"
        )
    )
VAR _wfhci =
    CALCULATE (
        MAX ( 'Table'[Check-in] ),
        FILTER (
            'Table',
            'Table'[Department] = _curdept
                && 'Table'[Employee Name] = _curemp
                && 'Table'[Mode Of Work] = "Work From Home"
        )
    )
VAR _wfoco =
    CALCULATE (
        MAX ( 'Table'[Check-out] ),
        FILTER (
            'Table',
            'Table'[Department] = _curdept
                && 'Table'[Employee Name] = _curemp
                && 'Table'[Mode Of Work] = "Work From Office"
        )
    )
VAR _wfhco =
    CALCULATE (
        MAX ( 'Table'[Check-out] ),
        FILTER (
            'Table',
            'Table'[Department] = _curdept
                && 'Table'[Employee Name] = _curemp
                && 'Table'[Mode Of Work] = "Work From Home"
        )
    )
RETURN
    IF (
        _count = 2,
        IF (
            _wfhdate = _wfodate
                && _wfoci = BLANK ()
                && _wfHci = BLANK ()
                && _wfoco = "TRUE"
                && _wfhco = "TRUE",
            "Error",
            IF (
                _wfhdate = _wfodate
                    && _wfoco = BLANK ()
                    && _wfHco = BLANK ()
                    && _wfoci = "TRUE"
                    && _wfhci = "TRUE",
                "Error",
                BLANK ()
            )
        ),
        IF (
            'Table'[Check-in] = "TRUE"
                && 'Table'[Check-out] = "TRUE",
            "Submitted",
            IF (
                'Table'[Check-in] = BLANK ()
                    && 'Table'[Check-out] = BLANK (),
                "Not Submitted",
                IF (
                    'Table'[Check-in] = "TRUE"
                        && 'Table'[Check-out] = BLANK (),
                    "Partial-In",
                    IF (
                        'Table'[Check-in] = BLANK ()
                            && 'Table'[Check-out] = "TRUE",
                        "Partial-out",
                        BLANK ()
                    )
                )
            )
        )
    )

Note: The data with red square are differnet with the one in your screenshot. Could you please provide some sample data in the table (exclude sensitive data) with Text format and your expected result with correct calculation logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards