Forum Discussion
How to remove names from column based on condition
| Class ID | Question | Question order | Alias | Avg Sat |
| 2 | Pre | 1 | Hari | 5 |
| 2 | post | 2 | prem | 4 |
| 2 | Pre | 3 | Suj | 5 |
| 2 | post | 4 | Har | 5 |
| 2 | Pre | 5 | Kal | 3 |
| 2 | post | 6 | KAl | 4 |
| 2 | Pre | 7 | Chen | 5 |
| 2 | post | 8 | Chen | 4 |
| 2 | Pre | 1 | Santosh | 3 |
| 2 | post | 2 | Santosh | 2 |
| 2 | Pre | 3 | Riti | 5 |
| 2 | post | 4 | Riti | 5 |
I have above table i want Dax querey which returns avg Sat if the Alias has answered both pre and post question.
Note that Questions are in pair 1 indicates pre and 2 indicates post applied to all other till 8.
Ne
Hi Harinihemanth06 ,
We could create formulas to get the data that contains both pre and post. It is not supported to remove values with DAX. There are two ways as workarounds that you can reference.
Firstly, create a column in the table.
Column = var d = CALCULATE(DISTINCTCOUNT('Table'[Question ]),ALLEXCEPT('Table','Table'[Alias])) return IF(d>1, "True", BLANK())- Create a measure to implement.
Measure = CALCULATE(MAX('Table'[Alias]),FILTER('Table','Table'[Column] = "True"))- Create a new table to implement
Table 2 = SELECTCOLUMNS ( FILTER ( 'Table', 'Table'[Column] = "True" ), "Class ID", 'Table'[Class ID], "Question", 'Table'[Question ], "Question Order", 'Table'[Question order], "Alias", 'Table'[Alias], "Avg sat", 'Table'[Avg Sat] )Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Sorry for not being clear about my question.
Let me explain
We have multiple Class ID and multiple Attendents to the class so When ever an individual is attending a class we are tracking
review by pre and post questions.
Here Question order indicates 1,3,5,7 as pre survey questions and 2,4,6,8 as post survey questions.
Avg Sat Value is valied if only individual answers both the questions. so my requirement is to remove Participents details who have not answered questions in pairs that is pre and post.
Hi Harinihemanth06 ,
We could create formulas to get the data that contains both pre and post. It is not supported to remove values with DAX. There are two ways as workarounds that you can reference.
Firstly, create a column in the table.
Column = var d = CALCULATE(DISTINCTCOUNT('Table'[Question ]),ALLEXCEPT('Table','Table'[Alias])) return IF(d>1, "True", BLANK())- Create a measure to implement.
Measure = CALCULATE(MAX('Table'[Alias]),FILTER('Table','Table'[Column] = "True"))- Create a new table to implement
Table 2 = SELECTCOLUMNS ( FILTER ( 'Table', 'Table'[Column] = "True" ), "Class ID", 'Table'[Class ID], "Question", 'Table'[Question ], "Question Order", 'Table'[Question order], "Alias", 'Table'[Alias], "Avg sat", 'Table'[Avg Sat] )Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-xuding-msft Thankyou So much for the Solution.It perfectly servers the puropse.
