Forum Discussion

Helper I

3 years ago

Solved

Condition based dax situation

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

v-yinliw-msft

3 years ago

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.

Condition based dax situation.pbix57 KB

Jatin77

Helper I

3 years ago

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

v-yinliw-msft

Community Support

3 years ago

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.

Condition based dax situation.pbix57 KB

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