cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Koliberus
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
v-polly-msft
Community Support
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?

 

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.

Thank You for taking interest in my problem 🙂

I'm attaching pbix example.

My data sources are Tale A and Table B

Koliberus_0-1665563235986.png

I created Valid Check Table like this:

Koliberus_1-1665563303585.png

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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

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