Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello all!
I have columns such as empid, name, test date, check date, subject, TR date. I want to write a dax for a calculated column which will return the 5 categories in a single column such as,
"test pending" - employees for which both test date and check date is blank for all the subjects for emp id
"Check pending" - if at least one test date is there for any of the subject for emp id
"Review pending" - if at least one test date is there for the subjects for emp id and there is no check date for emp id then these emp id should fall under "Review pending"
"Review pending" - if both test date and check date is not blank and check date is less than or equal to test date for emp id then these emp id should be under "Review pending"
(Review pending has the above two conditions – basically, if empid falls in “Check pending” as well as “Review pending” then that empid should show under both categories)
"No Action" - if emp id don't fall under "test pending" and "check pending" and "Review pending" and there is no TR date for that emp id then these emp id's should be under "No Action"
"Study required" - emp id's which don't fall under "test pending" and "check pending" and "Review pending" and TR date is not blank then these emp id's should fall under "Study required"
I have a similar table as below:
emp ID | name | subject | test date | check date | TR date |
1 | A | Math | 11/1/2015 | 5/2/2015 | 11/1/2014 |
1 | A | Science | 15/1/2015 |
| 11/1/2014 |
1 | A | KT | 15/1/2015 |
| 11/1/2014 |
1 | A | ENVR |
| 5/2/2015 | 11/1/2014 |
2 | B | Science | 11/1/2015 | 11/1/2015 |
|
2 | B | Math |
|
|
|
2 | B | KT |
|
|
|
2 | B | ENVR | 11/1/2015 | 11/1/2015 |
|
2 | B | JBP |
| 5/2/2015 |
|
2 | B | History | 15/1/2015 |
|
|
3 | C | Math |
| 11/1/2015 | 11/1/2014 |
3 | C | Science | 11/1/2015 |
| 11/1/2014 |
3 | C | KT |
| 5/2/2015 | 11/1/2014 |
4 | D | Math | 11/1/2015 | 11/1/2015 | 11/1/2014 |
5 | E | Math | 15/2/2015 | 5/3/2015 |
|
6 | F | Science |
|
|
|
6 | F | Math |
|
|
|
7 | G | Math | 15/1/2015 | 20/4/2015 | 11/1/2014 |
7 | G | Science | 15/1/2015 | 20/4/2015 | 11/1/2014 |
7 | G | KT | 11/1/2015 | 20/4/2015 | 11/1/2014 |
7 | G | ENVR | 11/1/2015 | 20/4/2015 | 11/1/2014 |
7 | G | JBP | 15/2/2015 | 20/4/2015 | 11/1/2014 |
output:
The cards should show such as follows:
Distinct count of emp id's for:
Test pending
1
Review pending
3
Check pending
3
No action
1
Study required
1
** Below description is the emp id falling under respective category: (for reference)
Test pending
(6)
Review pending
(1,3,4)
Check pending
(1,2,3)
No action
(5)
Study required
(7)
Solved! Go to Solution.
Hi @Jatin77 ,
Please refer to my pbix file to see if it helps you.
Create columns.
Column =
VAR _TEST =
MAXX (
FILTER ( ALL ( 'Sheet2' ), 'Sheet2'[name] = EARLIER ( 'Sheet2'[name] ) ),
'Sheet2'[test date]
)
VAR _CHACK =
MAXX (
FILTER ( ALL ( 'Sheet2' ), 'Sheet2'[name] = EARLIER ( 'Sheet2'[name] ) ),
'Sheet2'[check date]
)
VAR _testpending =
IF ( _CHACK = BLANK () && _TEST = BLANK (), "Test pending", BLANK () )
VAR _1 =
CALCULATE (
COUNT ( Sheet2[test date] ),
FILTER (
ALL ( Sheet2 ),
Sheet2[emp ID] = EARLIER ( Sheet2[emp ID] )
&& Sheet2[test date] <> BLANK ()
)
)
VAR _ckeckpending =
IF ( _1 <> BLANK (), "Check pending", BLANK () )
VAR _1a =
CALCULATE (
COUNT ( Sheet2[test date] ),
FILTER (
ALL ( Sheet2 ),
Sheet2[emp ID] = EARLIER ( Sheet2[emp ID] )
&& Sheet2[test date] <> BLANK ()
)
)
VAR _Reviewpending =
IF ( _1a >= 1 && Sheet2[check date] = BLANK (), "Review pending", BLANK () )
VAR _noaction =
IF (
_ckeckpending = BLANK ()
&& Sheet2[REVIEW PENDING1] = BLANK ()
&& _testpending = BLANK ()
&& Sheet2[TR date] = BLANK (),
"No Action",
BLANK ()
)
VAR _studyrequired =
IF (
_ckeckpending = BLANK ()
&& Sheet2[REVIEW PENDING1] = BLANK ()
&& _testpending = BLANK ()
&& Sheet2[TR date] <> BLANK (),
"Study required",
BLANK ()
)
RETURN
IF (
_testpending <> BLANK (),
_testpending,
IF (
_Reviewpending <> BLANK ()
|| _ckeckpending <> BLANK (),
Sheet2[REVIEW PENDING1] & _ckeckpending,
IF (
_noaction <> BLANK (),
_noaction,
IF ( _studyrequired <> BLANK (), _studyrequired, BLANK () )
)
)
)
How to Get Your Question Answered Quickly
Please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Jatin77 ,
Please refer to my pbix file to see if it helps you.
Create columns.
Column =
VAR _TEST =
MAXX (
FILTER ( ALL ( 'Sheet2' ), 'Sheet2'[name] = EARLIER ( 'Sheet2'[name] ) ),
'Sheet2'[test date]
)
VAR _CHACK =
MAXX (
FILTER ( ALL ( 'Sheet2' ), 'Sheet2'[name] = EARLIER ( 'Sheet2'[name] ) ),
'Sheet2'[check date]
)
VAR _testpending =
IF ( _CHACK = BLANK () && _TEST = BLANK (), "Test pending", BLANK () )
VAR _1 =
CALCULATE (
COUNT ( Sheet2[test date] ),
FILTER (
ALL ( Sheet2 ),
Sheet2[emp ID] = EARLIER ( Sheet2[emp ID] )
&& Sheet2[test date] <> BLANK ()
)
)
VAR _ckeckpending =
IF ( _1 <> BLANK (), "Check pending", BLANK () )
VAR _1a =
CALCULATE (
COUNT ( Sheet2[test date] ),
FILTER (
ALL ( Sheet2 ),
Sheet2[emp ID] = EARLIER ( Sheet2[emp ID] )
&& Sheet2[test date] <> BLANK ()
)
)
VAR _Reviewpending =
IF ( _1a >= 1 && Sheet2[check date] = BLANK (), "Review pending", BLANK () )
VAR _noaction =
IF (
_ckeckpending = BLANK ()
&& Sheet2[REVIEW PENDING1] = BLANK ()
&& _testpending = BLANK ()
&& Sheet2[TR date] = BLANK (),
"No Action",
BLANK ()
)
VAR _studyrequired =
IF (
_ckeckpending = BLANK ()
&& Sheet2[REVIEW PENDING1] = BLANK ()
&& _testpending = BLANK ()
&& Sheet2[TR date] <> BLANK (),
"Study required",
BLANK ()
)
RETURN
IF (
_testpending <> BLANK (),
_testpending,
IF (
_Reviewpending <> BLANK ()
|| _ckeckpending <> BLANK (),
Sheet2[REVIEW PENDING1] & _ckeckpending,
IF (
_noaction <> BLANK (),
_noaction,
IF ( _studyrequired <> BLANK (), _studyrequired, BLANK () )
)
)
)
How to Get Your Question Answered Quickly
Please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Jatin77 ,
"Check pending" - if at least one test date is there for any of the subject for emp id.
In the output, you want the result is id (1,2,3). Why do not (4,5,7) count? When neither the check data not the test date for 4 and 7 are blank, and the test date for 5 is not blank?
4 | D | Math | 2015/1/11 | 2015/1/11 | 2014/1/11 |
5 | E | Math | 2015/2/15 | 2015/3/5 |
7 | G | Math | 2015/1/15 | 2015/4/20 | 2014/1/11 |
7 | G | Science | 2015/1/15 | 2015/4/20 | 2014/1/11 |
7 | G | KT | 2015/1/11 | 2015/4/20 | 2014/1/11 |
7 | G | ENVR | 2015/1/11 | 2015/4/20 | 2014/1/11 |
7 | G | JBP | 2015/2/15 | 2015/4/20 | 2014/1/11 |
"Review pending" - if both test date and check date is not blank and check date is less than or equal to test date for emp id then these emp id should be under "Review pending".
In the output, you want the result is id (1,4,3). Why (2,5,7) not included?
2 | B | Science | 2015/1/15 | 2015/1/11 |
5 | E | Math | 2015/2/15 |
2015/3/5 |
7 | G | Math | 2015/1/15 | 2015/4/20 | 2014/1/11 |
7 | G | Science | 2015/1/15 | 2015/4/20 | 2014/1/11 |
7 | G | KT | 2015/1/11 | 2015/4/20 | 2014/1/11 |
7 | G | ENVR | 2015/1/11 | 2015/4/20 | 2014/1/11 |
7 | G | JBP | 2015/2/15 | 2015/4/20 | 2014/1/11 |
How to Get Your Question Answered Quickly
Please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @Anonymous ,
Thank you for the reply,
I might have made a mistake while providing a reference but the logic explained is correct. I have tried uploading the pbix file but there is no option for the same and below is the dax I have tried:
Hi @Jatin77
Please try
Category =
VAR CurrentEmpTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[emp ID] ) )
VAR Test_Pending =
ISEMPTY (
FILTER (
CurrentEmpTable,
'Table'[test date] <> BLANK ()
&& 'Table'[check date] <> BLANK ()
)
)
VAR Review_Pending =
NOT ISEMPTY (
FILTER (
CurrentEmpTable,
'Table'[test date] <> BLANK ()
&& OR (
'Table'[check date] <> BLANK (),
'Table'[check date] <= 'Table'[test date]
)
)
)
VAR Check_Pending =
NOT ISEMPTY ( FILTER ( CurrentEmpTable, 'Table'[test date] <> BLANK () ) )
VAR No_Action =
ISEMPTY ( FILTER ( CurrentEmpTable, 'Table'[TR date] <> BLANK () ) )
RETURN
SWITCH (
TRUE (),
Test_Pending, "Test Pending",
Review_Pending, "Review Pending",
Check_Pending, "Check pending",
No_Action, "No Action",
"Study Required"
)
Hi @tamerj1 , thank you for replying
@Jatin77
Then I misunderstood the logic. Can you please add the expected results to the same sample data provided?
Hi @tamerj1 ,
I have updated the data and required output and will also upload the dax which I have tried as it is giving a required solution only the problem is that if emp id is firstly getting calculated under check pending and if the same emp id is also falling under review pending then in such cases the emp id is only getting considered under check pending and not considered under review pending which is wrong, as such emp id should get counted under both i.e. check pending and review pending
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
41 | |
31 | |
23 | |
23 | |
22 |