cancel
Showing results for
Did you mean:
Frequent Visitor

## Column with count of email adress connected to serial number

Hello everyone,

Got problem with adding column that shows count of email adresses connected to serial number.
I have to seperated data sets (lets call them set A and B - both have "Serial Number" and "Email Adress" columns - relation is setted on "Serial Number"
I'm trying to get "valid check" columns between both.

I created separate Main Table in with:
- First column pull out with DISTINCT Serial Numbers from A Data;

- Second column COUNT number of B Data Serial Numbers in A Data based on first column;

- Third column check number of duplicated Serial Numbers in A Data;

- Fourth column does TRUE FALSE check for count of Serial Numbers in second and third count;

Now i need to create two separate columns that will show count of email adresses connected to serial numbers one from A and second from B Data in Main Table.

Aaaand... i'm stuck with that 😞

2 REPLIES 2
Community Support

Hi @Koliberus ,

I cannot understand your meaning correctly, could you please provide more details with the desired output and pbix file without privacy information?

Best Regards

Community Support Team _ Polly

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

Frequent Visitor

Thank You for taking interest in my problem 🙂

I'm attaching pbix example.

My data sources are Tale A and Table B

I created Valid Check Table like this:

Formulas i used in this Table for specific columns are:

1. Serial Number = DISTINCT('Table A'[Serial Number])
2. Table B SN Count =
COUNTROWS (
FILTER (
'Table B',
'Table B'[Serial Number] = EARLIER ( [Serial Number] )
)
) +0

3. Table A SN Count =

COUNTROWS (
FILTER (
'Table A',
'Table A'[Serial Number] = EARLIER ( [Serial Number] )
)
) +0

4. Valid SN = if('Valid Check'[Table A SN Count]=1 && 'Valid Check'[Table B SN Count]=1, 1, 0)

What i need now is to create three new columns in Valid Check Table:
- Count of emails for each Serial Number from Table A
- Count of emails for each Serial Number from Table B

- true/false if emails counts for specific Serial Number from both Tables are the same or not

Best Regards
Patryk Kopyczynski

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors