Create New Column

[chonchar]

@parry2k @Erokor @MarceloVieira 

Okay, let’s try this again. Apologies to the users that helped me yesterday, as I did not prepare all the information.

I have a table called “Master Login” which holds the primary key, or the 1 side of the relationship. Within this table, there is a column called “Email Domain”.

I then have another table called “Fiscal ’22 Lookup” which has the many (*) side of a relationship.

I would like to pull the email domains FROM “Master Login” into a new column INTO the “Fiscal ’22 Lookup” table.

After that is complete, I would like to visualize it as seen above.

Then, I would like to make a new table that only include the “email domain” and have a count on the right-hand side of it that counts how many occurrences the domain appears in the “Fiscal ’22 Lookup” table.

Thank you,

Cam

[parry2k]

@chonchar not sure why we are making it super complicated or I'm missing something:

In visual, just use domain from the master table and countrows of fact table (add as a measure) and that should do it.

Number of Rows = COUNTROWS ( TableFact )

[Erokor]

Thanks for the update. To start with your table "Master Login" cannot possibly be on the 1 side of a 1 to many, as it has duplicates in the column "Email Domain"

[chonchar]

@Erokor It is on the 1 side of a relationship. The Primary Key is the User ID, which is unique. Multiple users can have the same email domain. 

[Erokor]

Thank you for the feedback. This wasn't apparent in the first screenshot - would you have multiple records for the user? I'm going to assume not in order to give you a timely response:

In PowerQuery, If you were to use the "Merge" function on the User ID column in the "Fiscal 2022" table and select the the User ID column in the "Master Login" Table (containing a column called User ID not pictured in your screenshot) and then "Expand" the resulting column, it would bring in any selected columns you choose from the "Master Login" table Beware, if you have duplicate User ID's in the Master Login table, you will get multiple records returned in the Fiscal 2022 table.

Following this - in the canvas of the report - you'll need to simply drag the Domain name into a Matrix or table visual. Then you'll either want to write a measure that counts the rows of the newly combined domain table. COUNTROWS(NewTable). and place it in the same visual, or simply drag an item in from that table and change the aggregation to COUNT.