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.
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:
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 - Jatin773 years ago
Helper I
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/2015And 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 **
- 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.