Forum Discussion

Helper I

3 years ago

Solved

Multi-condition dax for 5 categories

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)

Anonymous

3 years ago

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.

Multi-condition dax for 5 categories.pbix71 KB

7 Replies

Anonymous

Not applicable

3 years ago

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.

Multi-condition dax for 5 categories.pbix71 KB

tamerj1

Community Champion

3 years ago

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
Helper I
3 years ago
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
  Community Champion
  3 years ago
  Jatin77
  Then I misunderstood the logic. Can you please add the expected results to the same sample data provided?

Anonymous

Not applicable

3 years ago

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?

Science

2015/1/15

2015/1/11

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
Helper I
3 years ago
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:

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

Forum Discussion

Multi-condition dax for 5 categories

7 Replies

Related content

DAX : Based on condition on multi column

Multi Row Card Conditional Formatting

DAX help on category

Multi Row Card Conditional Formatting

Dax