Column with count of email adress connected to serial number

[Koliberus]

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 😞

[v-rongtiep-msft]

Hi @Koliberus ,

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

How to Get Your Question Answered Quickly 

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.

[Koliberus]

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