Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
2 | |
2 | |
1 | |
1 |