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.

Frequent Visitor

## Count the number of unique entries(string) per category

Hi All,

I have the following table structure:

 Itemgroup Itemno A 1 A 2 A 2 B 3 B 3 B 3 C 4 C 5 C 6

And the output I need is a calculated column that contains the unique number of Itemno for each Itemgroup.
Like this:

 Itemgroup Itemno Unique Entries of Itemno A 1 2 A 2 2 A 2 2 B 3 1 B 3 1 B 3 1 C 4 3 C 5 3 C 6 3

I think the answer would be some sort of combination of SUMMARIZE/GROUPBY and DISTINCT COUNTROWS.  I just cannot figure this one out myself.

1 ACCEPTED SOLUTION
Community Support

Hi  @CSRiotech ,

Here are the steps you can follow：

1. Create calculated column.

``````Unique Entries of Itemno =
CALCULATE(
DISTINCTCOUNT('Table'[Itemno]),
FILTER(ALL('Table'),
'Table'[Itemgroup]=EARLIER('Table'[Itemgroup])))``````

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Community Support

Hi  @CSRiotech ,

Here are the steps you can follow：

1. Create calculated column.

``````Unique Entries of Itemno =
CALCULATE(
DISTINCTCOUNT('Table'[Itemno]),
FILTER(ALL('Table'),
'Table'[Itemgroup]=EARLIER('Table'[Itemgroup])))``````

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

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