Condition based dax situation

[Jatin77]

Hello!
I have a following data and I want to write a dax:

ID	name	subject	test date	check date	O/p
1	A	Math	11/1/2015	5/2/2015	retake the test
1	A	Science	12/1/2015		retake the test
1	A	KT	12/1/2015		retake the test
1	A	ENVR		5/2/2015	retake the test
2	B	Science	11/1/2015	11/1/2015	retake the test
2	B	Math			retake the test
2	B	KT			retake the test
2	B	ENVR	11/1/2015	11/1/2015	retake the test
2	B	JBP		5/2/2015	retake the test
2	B	History	12/1/2015		retake the test
3	C	Math			test pending
3	C	Science			test pending
3	C	KT			test pending
4	D	Math	11/1/2015	9/1/2015	check pending
5	E	Math	12/2/2015	12/2/2015	check pending
6	F	Science			retake the test
6	F	Math	11/1/2015		retake the test
7	G	Math			retake the test
7	G	Science	12/1/2015	12/1/2015	retake the test
7	G	KT	11/1/2015	5/2/2015	retake the test
7	G	ENVR			retake the test
7	G	JBP	12/2/2015	5/2/2015	retake the test

In above table O/p column is what I want to achieve and below are he conditions for it:

o/p-Conditions-> cond 1 should be checked first if not met then it should move to 2nd and so on, but if 1st cond is met then it should stop ther only and reflect that result in all cells under o/p column for that emp
1) If both test and review date is blank for all the subject for that particular employee only then a column cell should show "test pending"
2) If any of the cell from test date or check date has a date then the whole cells under that employee should turn to "retake the test"
3) If both the columns i.e. test date and check date has a date and if the check date is less than or equal to test date then it should show "check pending"
4) Else "NA"

Thanks!

[ERD]

@Jatin77 , please, look carefully at your resulting column and the conditions you've provided. It's hard to understand what you want to achieve in the result. E.g.:

If both test and review date is blank for all the subject for that particular employee only then a column cell should show "test pending"

Looking at the sample you've provided, you should not have 'test pending' anywhere since you do not have an employee with ALL blank dates for ALL subjects.

Here is an example of the DAX you can use assuming the conditions you've written are true.

o/p result =
VAR current_employee = SELECTEDVALUE ( Table[name] )
VAR not_empty =
    COUNTROWS (
        CALCULATETABLE (
            Table,
            Table[name] = current_employee && Table[test date] <> BLANK () || Table[check date] <> BLANK (),
            ALL ( Table[subject] )
        )
    )
VAR current_check_date = SELECTEDVALUE ( Table[check date] )
VAR current_test_date = SELECTEDVALUE ( Table[test date] )
RETURN
    SWITCH (
        TRUE (),
        not_empty = 0, "test pending",
        current_check_date <> BLANK () && current_test_date <> BLANK ()
            && current_check_date < current_test_date, "check pending",
        current_check_date <> BLANK () || current_test_date <> BLANK (), "retake the test",
        "NA"
    )

Try to play with SWITCH conditions if the actual conditions are different.

[Jatin77]

Hello @ERD ,

Thank you for the reply. I have updated the data and made slight changes as well. I have also tried the dax which you have mentioned. Sharing the dax and o/p I'm getting:

----------------------------------------------------------------------------------------------------------------------

o/p result =

VAR current_employee = SELECTEDVALUE ( Sheet4[name] )

VAR not_empty =

    COUNTROWS (

        CALCULATETABLE (

            Sheet4,

            Sheet4[name] = current_employee && Sheet4[test date] <> BLANK () || Sheet4[check date] <> BLANK (),

            ALL ( Sheet4[subject] )

        )

    )

VAR current_check_date = SELECTEDVALUE ( Sheet4[check date] )

VAR current_test_date = SELECTEDVALUE ( Sheet4[test date] )

RETURN

    SWITCH (

        TRUE (),

        not_empty = 0, "test pending",

        current_check_date <> BLANK () && current_test_date <> BLANK ()

            && current_check_date < current_test_date, "check pending",

        current_check_date <> BLANK () || current_test_date <> BLANK (), "retake the test",

        "NA"

    )

----------------------------------------------------------------------------------------------------------------------
--> o/p:

Thanks an Regards,
Jatin77

[ERD]

@Jatin77 , there might be a better way, but you can try this measure:

o/p result = 
VAR current_employee = SELECTEDVALUE ( Sheet4[name] )
VAR wrong_rows =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Sheet4, Sheet4[name], Sheet4[test date], Sheet4[check date] ),
            "i_value",
                SWITCH (
                    TRUE (),
                    ( Sheet4[test date] = BLANK () && Sheet4[check date] <> BLANK () )
                        || ( Sheet4[test date] <> BLANK () && Sheet4[check date] = BLANK () )
                        || Sheet4[check date] > Sheet4[test date], 
                    1,
                    Sheet4[test date] <> BLANK () && Sheet4[check date] <> BLANK ()
                        && Sheet4[check date] <= Sheet4[test date], 
                    2,
                    0
                )
        ),
        Sheet4[name] = current_employee,
        ALL ( Sheet4 )
    )
RETURN
    SWITCH (
        TRUE (),
        SUMX ( wrong_rows, [i_value] ) = 0, "test pending",
        MINX ( wrong_rows, [i_value] ) = 2, "check pending",
        AVERAGEX ( wrong_rows, [i_value] ) > 0 && AVERAGEX ( wrong_rows, [i_value] ) < 2, "retake the test",
        "NA"
    )

I've also added 2 more rows to check more options that might accur:

As for the 'Table_5', I just forgot to rename it, sorry.

[Jatin77]

Hi @ERD,
Thank you for the reply. Tried this dax but it is not working for me.

Thanks and Regards,
Jatin77

[v-yinliw-msft · Accepted solution]

Hi @Jatin77 ,

You can try this method:

O/p =
VAR _BlankT =
    CALCULATE (
        MAX ( 'Table'[test date] ),
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    )
VAR _BlankC =
    CALCULATE (
        MAX ( 'Table'[test date] ),
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    )
VAR _CountT =
    CALCULATE ( COUNTA ( 'Table'[test date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
VAR _CountC =
    CALCULATE ( COUNTA ( 'Table'[check date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
VAR _TotalCountID =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
    SWITCH (
        TRUE (),
        _BlankC = BLANK ()
            && _BlankT = BLANK (), "test pending",
        _CountT <> _TotalCountID
            || _CountC <> _TotalCountID, "retake the test",
        _CountT = _TotalCountID
            && _CountC = _TotalCountID
            && 'Table'[check date] <= 'Table'[test date], "check pending",
        BLANK (), "N/A"
    )

The result is:

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jatin77]

Hi @v-yinliw-msft @ERD @FreemanZ 

I have one more similar kind of a situation like -

I have two more columns named "status" which have yes or no or blanks and "color" which have either yellow/blue

I want to calculate the no of distinct id's for following condition -

Color: yellow
Date: 11/01/2015

And in the status column "yes" should be there for all the subjects, if for that id, any of the subject has any other value then it should not select that id

** The selected distinct id's should have "yes" for all the subjects **

[Jatin77]

Hi @v-yinliw-msft,

Thank you for the reply. This solution worked on sample data but in original data, there's an additional condition occcured i.e. the o/p column says - "blank" if "test date and check date both are blank" or ""test date and check date both have a date" wherein blanks should only come for the condition if "check date>test date"

Thanks and Regards,
Jatin77

[Jatin77]

Thanks @FreemanZ 

I have updated the data now.

[FreemanZ]

the O/P column seems contradictary with the conditions. or?