Calculate and filter not working

[Anonymous]

Hello All, 

I am running some survey stats and have a simple aggregate measure. I am looking to calculate the number of codes in the dataset which have less than 70% respondent agreement. I am using the following dax: 

% Agree <70 = CALCULATE(COUNT(Sheet1[course_code]),FILTER(Sheet1,[11 % Agree Col]<.7))

Its not producing the right answers, the dataset is set up that there may be multiple records for each code, one record for each respondent. 

What am I doing wrong?

[Anonymous]

Hey @Anonymous

Maybe you can try this:

% Agree <70 = CALCULATE(COUNT(Sheet1[course_code]),FILTER(ALLSELECTED(Sheet1),[11 % Agree Col]<.7))

The only difference is that I added ALLSELECTED to your filter. Might want to try ALL if that doesn't work. Let me know if this helps.

-Parker

[Anonymous]

Thanks Parker neither worked 😞

[Anonymous]

How are you displaying this measure? A card visual?

Also, are you just trying to get the distinct number of codes? In which case you might want to use the DISTINCTCOUNT function

[Anonymous]

Thanks Parker, distictcount doesnt seem to want to work either. Not at all sure why. 

[v-yulgu-msft]

Hi @Anonymous，

Please try this:

% Agree <70 = CALCULATE(COUNT(Sheet1[course_code]),FILTER(ALL(Sheet1),[11 % Agree Col]<.7))

If it still doesn't work, please post sample data and show us your desired output so that I can test on my side.

Regards,

Yuliana Gu

[Anonymous]

Thanks Guys, 

This didnt work. 

my Data is structured like this:

Respondent ID | Category | Code | Agree or Disagree

e.g. 

123455| MED | 1XY45 | Agree

123344| MED | 1XY45 | Disagree

I want to be able to calculate % Agreement of each code (in this case 1XY45) and then count the number of codes that have less than 70% agreement by category. 

So the simple example above would give me 50% agreement for code 1XY45 and 1 survey with less than 70% agreement for the category MED

I seem to be able to calcualte the % Agreement but cant seem to be able to calculate and filter on the % agree measure.

Maybe I need to sumamrise the data?

Thanks so much, 

Keelin

[Zubair_Muhammad]

@Anonymous

Give this a shot

% Agree <70 =
COUNTROWS (
    FILTER ( ALLSELECTED ( Sheet1[course_code] ), [11 % Agree Col] < .7 )
)

[Anonymous]

% Agree <70 =
COUNTROWS (
    FILTER ( ALLSELECTED ( Sheet1[course_code] ), [11 % Agree Col] < .7 )
)

@Zubair_Muhammad 

Hi  Zubair_Mohamad, this is bringing back the overall result of the number of course codes <.7.  It breaks when I try to use the measure to show results by category in a table. I cant seem to work in the categories, for example the simple MED example above. Do you know how I work other filters into this?  Seems like we are part of the way there. 

Thanks

Keelin

[Zubair_Muhammad · Accepted solution]

@Anonymous

In that case.....Try Replacing allselected with VALUES

% Agree <70 =
COUNTROWS ( FILTER ( VALUES ( Sheet1[course_code] ), [11 % Agree Col] < .7 ) )

[Anonymous]

@Zubair_Muhammad you're a rock star!

Thanks so much for this, it worked. I'll be able to deliver the report on time now. Thanks again

[Anonymous]

@Zubair_Muhammadis a good solution. The alternate is there:

% Agree <70 = CALCULATE(
	COUNTROWS(Sheet1),
	ALLSELECTED(Sheet1)
	[11 % Agree Col] < 0.7
)

I am a big proponant of avoiding mixing Filter and Calculate together unless absolutely necessary.  99% of the time adding filter to calculate is redundant.