Group Values

[CoQu9]

Hi all, 

I am a relatively experienced Power BI/Power Query user but I am running into a problem with a new dataset I am working with. The data has repeat values and I need to report out things such as, "how many states have a F employee". I'm struggling with how to do this in Power Query or Power BI. Is there a calculation that would automatically group all three CA rows so that if I asked for a count of F it would say 2? 

Work Site	Function	Gender
CA	PR	F
CA	Eng	F
CA	HR	M
MT	HR	M
MT	HR	F
TX	ENG	M

Thank you!

[Anonymous · Accepted solution]

I would group by State AND Gender, and then add a Count Aggregation.

--Nate 

[BA_Pete · Accepted solution]

Hi @CoQu9 ,

You can create DISTINCTCOUNT measures to tackle this particular issue.
For example, the following will output 2:

_StatesWithF = 
CALCULATE(
    DISTINCTCOUNT('Table'[Work Site]),
    'Table'[Gender] = "F"
)

My example measure is specific to this one requirement, but the use of DISTINCTCOUNT is what you're looking for to solve your overall need.

Pete

[Anonymous · Accepted solution]

I would group by State AND Gender, and then add a Count Aggregation.

--Nate