DAX DISTINCTCOUNT Help needed

[PaulMac]

Hello all

I need some assitance with creating a DAX measure to do the following.

count the number of people who have scored more than or equal to 4, more than or equal to 3 or less than 3.

As can be seen in the SampleData file, I have filtered the data to only show those who have an average score below 3.

10 names appear in the filtered table, but the only DAX I have been able to produce give me a count of 13!!??

I need to produce a DAX measure that will correctly do a distinct count of the Names to give a numerical value of those whose Average QA score is above or below a certain threshold.

I need to learn how to accomplish this in Power BI using a DAX measure.

Click Here to see attached SampleData file to illustrate what I am trying to accomplish.

Thanks

PaulMc

[RobbeVL · Accepted solution]

Hi,

Here I calcuated who has an average score ❤️

Measure = 
COUNTX(
    FILTER(
    SUMMARIZE('Table'; 'Table'[Name]; "av"; AVERAGE('Table'[Score]));
    [av] <3); 
DISTINCTCOUNT('Table'[Name]))

There might be an easier way, but this will work 🙂

Robbe

[PaulMac]

@RobbeVL Could explain why you used [av] and what does it do?

[PaulMac]

@RobbeVL  You sir, are a wizard!!

This is the second time this week you have helped me out!! I owe a coffee!! 

In all seriousness. do you have any online learning resources for DAX as I obviously need some further training in this field.

Many thanks again.

PaulMc

[RobbeVL]

I'm sure you'll find alot on youtube.
Udemy also has a few cheap trainings that might be of use. But make sure you do a PowerBI DAX training, PowerBI Doesnt support all DAX Functions.

[PaulMac]

Many thanks I will check those out.

Also, why did you use [av]? 

[RobbeVL]

Check out the SUMMARY function a bit more!

Comes to use quite often.

What I actually did with your calculation is the following:
 

SUMMARIZE: Create a virtual table from the table "table", group based on Name, THEN you add your calculation, in your case average Score.

Now you have a table with the names and next to it the column [av] (I named it that in summarize).

Next is Filtering that table to a avg score ❤️ by using the FILTER function.

Finally I want to convert my table to a single value. So I have to countx the remaining names in my table.

I hope this helps a litte ? 

Robbe