topic Re: DAX query to filter out unmatched values in two columns in DAX Commands and Tips

DAX query to filter out unmatched values in two columns

funlova- — Sat, 08 May 2021 02:31:56 GMT

Please see below the context of the question and the screenshot attached.

Overview

Before and after a coaching program, clients would fill an assessment/survey

definition of the attributes

Assessment id- is the unique id for a client

Assessment Phase: Initial - assessment before the program, Final - assessment after the program

Year/month: when the assessment was done

Self Answered initial : a measure that displays 1 if a client filled the initial survey

Self Answered Final: a measure that displays 1 if a client filled the final survey.

**The goal is to be able to filter only the clients that filled both surveys. E.g. The first allowance id- FFE1E8B3-86AC-4D1D-B31B-2CD6C51F22C9 filled both the initial and final survey. **

However some clients like FFB55230-DE36-4430-90F5-C20B53CA8724 have only completed the initial survey.(they should be filtered out)

Can anyone help provide the right functions to solve this problem. I am a newbie Power BI user😊

Thanks for your help.

Re: DAX query to filter out unmatched values in two columns

rfigtree — Sat, 08 May 2021 02:43:12 GMT

Countrows(filter(table,[selffinalnswer]=1))

On phone cant type well.

Re: DAX query to filter out unmatched values in two columns

Anonymous — Sat, 08 May 2021 05:39:44 GMT

Since you are new user, I would suggest a simple solution of putting filter on these measures in filter pane on Power BI Desktop. Put both filter conditions as below:

Self Answered initial=1

Self Answered final=1

Or you can create a third measure as below:

MeasureFIlterCustomer= IF(Self Answered initial=1 AND Self Answered final = 1, 1, 0)

Now put above measure in filter pane.

Let me know if you need any help. Mark this reply as solution if it answers your question.

Re: DAX query to filter out unmatched values in two columns

funlova- — Sat, 08 May 2021 20:08:21 GMT

Thank Megha166 for your response.

I created a measure

Filtercheck = IF(AND(ProdProgressTrackerAnswers[# Self Answered Initial] =1,ProdProgressTrackerAnswers[# Self Answered Final] =1), "1", "0") but when I added it to Fields, all the rows were filtered as 0 and the IDs were duplicated. I think maybe select MAX Id would help with the duplication issue. I have attached a screen shot.

Re: DAX query to filter out unmatched values in two columns

Anonymous — Sun, 09 May 2021 06:28:28 GMT

I see IDs are not duplicated. I need to understand your data in detail why IDs are getting duplicated. Can you attach the data in excel file here?

Re: DAX query to filter out unmatched values in two columns

funlova- — Sun, 16 May 2021 13:00:24 GMT

Hi Anonymous ,

Thank you for your response. I have added two screenshot below.

In the first table, #self answered is the count of people who filled the initial survey.

In the second table, #self answered is the count of people who filled the final survey.

I will like to see only the people that filled both the initial and final survey. This will help me to see the unique average score for the respondents. Currently, it is biased towards the final phase because we have more respondents in the intial phase

Which DAX function can I use to solve this problem?

Thank you.