Multi-condition dax for 5 categories

[Jatin77]

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)

[v-rongtiep-msft · Accepted 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.

[v-rongtiep-msft]

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.

[Jatin77]

Hello @v-rongtiep-msft ,
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:

info =

var td =

        CALCULATE(MAX(Sheet2[test date]),

        FILTER(Sheet2,Sheet2[emp ID]=EARLIER(Sheet2[emp ID]))

        )

var cd =

        CALCULATE(MAX(Sheet2[check date]),

        FILTER(Sheet2,Sheet2[emp ID]=EARLIER(Sheet2[emp ID]))

        )

var count_td = CALCULATE(COUNTA(Sheet2[test date]), ALLEXCEPT(Sheet2, Sheet2[emp ID]))

var count_cd = CALCULATE(COUNTA(Sheet2[check date]), ALLEXCEPT(Sheet2, Sheet2[emp ID]))

var total_ct = CALCULATE(COUNTROWS(Sheet2), ALLEXCEPT(Sheet2,Sheet2[emp ID]))

var z1 = SWITCH(

    TRUE(),

    td=BLANK() && cd=BLANK(), "test pending",

    count_td=total_ct && count_cd=total_ct && td<=cd, "review pending",

    count_td<>total_ct || count_cd<>total_ct, "check pending",

    "study required"

)

return

    IF(td=BLANK() && cd=BLANK(), "test pending",

    IF(count_td<>total_ct || count_cd<>total_ct, "check pending",

        IF(

            OR(

                count_td=total_ct && count_cd=total_ct && td<=cd, z1="check pending" && count_cd<>cd), "review pending",

                IF(z1="study required" && Sheet2[TR date]=BLANK() && COUNTROWS(FILTER(Sheet2, Sheet2[emp ID] = EARLIER(Sheet2[emp ID]))), "no action",

        "study required"

                )

            )

        )

    )

[tamerj1]

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"
    )

[Jatin77]

Hi @tamerj1 , thank you for replying 

• Tried the above dax but in o/p, emp id's which should fall under Check pending arey all falling under Review Pending and no emp id is falling under no action and study required
•  

[tamerj1]

@Jatin77 
Then I misunderstood the logic. Can you please add the expected results to the same sample data provided?

[Jatin77]

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