Forum Discussion
Condition based dax situation
- 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.
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.
- Jatin773 years ago
Helper I
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] )RETURNSWITCH (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-msft3 years ago
Community Support
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.
- Jatin773 years ago
Helper I
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
- ERD3 years ago
Community Champion
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.