Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Jatin77
Helper I
Helper I

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)

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

vpollymsft_0-1674006270201.png

 

 

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.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

vpollymsft_0-1674006270201.png

 

 

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.

Anonymous
Not applicable

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:

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
Super User
Super User

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 

  • 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
  •  

@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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.