cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Calculate the number of unique values in a column and no value in another column

Hi Everyone!

How can I calculate the number of departments with no FTE value but also check that no projects have no FTE value?

So my desired result is 1 because Department 2 has no value in Project B and Project A.

There's only 1 department that has no FTE value from all of the projects. Basically, I want to check how many unique departments that have no FTE value across all projects.

Thank you.

- Third

1 ACCEPTED SOLUTION
Resolver II

Hi,

I think this formula should help you:

Distinct Count =
VAR __Table = FILTER(
SUMMARIZE('Table',
'Table'[Department],
'Table'[Project],
"@FTE",
CALCULATE(
SUM('Table'[FTE]),
ALLEXCEPT('Table','Table'[Department])
)
),
[@FTE] = BLANK()
)
RETURN
CALCULATE(DISTINCTCOUNT('Table'[Department]), __Table)
I added one more department (6) with one project non-blank to proof the formula.

Hope it helps!
2 REPLIES 2
Helper III

@StrategicSavvy

Thank you very much for your help!

Resolver II

Hi,

I think this formula should help you:

Distinct Count =
VAR __Table = FILTER(
SUMMARIZE('Table',
'Table'[Department],
'Table'[Project],
"@FTE",
CALCULATE(
SUM('Table'[FTE]),
ALLEXCEPT('Table','Table'[Department])
)
),
[@FTE] = BLANK()
)
RETURN
CALCULATE(DISTINCTCOUNT('Table'[Department]), __Table)
I added one more department (6) with one project non-blank to proof the formula.

Hope it helps!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors