Forum Discussion

Jatin77's avatar
Jatin77
Icon for Helper I rankHelper 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,...
  • Anonymous's avatar
    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.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity 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's avatar
Jatin77
Icon for Helper I rankHelper 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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

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

    • Jatin77's avatar
      Jatin77
      Icon for Helper I rankHelper I
      3 years ago

      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