Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
I would group by State AND Gender, and then add a Count Aggregation.
--Nate
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
I would group by State AND Gender, and then add a Count Aggregation.
--Nate