How to remove names from column based on condition

[Harinihemanth06]

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

[Harinihemanth06]

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.

[v-xuding-msft · Accepted solution]

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.

[Harinihemanth06]

@v-xuding-msft Thankyou So much for the Solution.It perfectly servers the puropse. 

[Harinihemanth06]

@v-xuding-msft There is a minor error if Alias has given rating to all the questions except question 2  i.e 1,2 are pre and post and example :Lars(alias) has given rating for question 1 , question 3 and question 4.

Now according to provided input 

Column = 
var d = CALCULATE(DISTINCTCOUNT('Table'[Question ]),ALLEXCEPT('Table','Table'[Alias]))
return 
IF(d>1, "True", BLANK())

Above will return blank if d>1, But now Lars( Alias) has 3 count it is not getting filtered.