Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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 , 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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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
@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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @ERD,
Thank you for the reply. Tried this dax but it is not working for me.
Thanks and Regards,
Jatin77
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.
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 **
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
the O/P column seems contradictary with the conditions. or?
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |