Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
gvlado
Helper II
Helper II

How many times data from column appears in other column

Hello PBI friends
I have, probably simple, problem but I am stucked and going around like in rounabout road.
Have to produce column that will be based on appearance of the same data (for example from column 2) in another column ( for example column 3).
Example:
Product A can be sold in many countries. I have to know in how many countries it is sold? Desired solution is Apperas in #Country (green header)

gvlado_0-1695032662092.png

Thanks, a lot. I am still learning so I hope once I will be able to help to someone.

1 ACCEPTED SOLUTION
ChiragGarg2512
Solution Sage
Solution Sage

Creation of the following column can help you with this situation.

 

Column = calculate(DistinctCOUNT('Table'[Country]), Filter('Table', 'Table'[Product] = EARLIER('Table'[Product])))

View solution in original post

5 REPLIES 5
ChiragGarg2512
Solution Sage
Solution Sage

Creation of the following column can help you with this situation.

 

Column = calculate(DistinctCOUNT('Table'[Country]), Filter('Table', 'Table'[Product] = EARLIER('Table'[Product])))

I knew it - so simple
Thanks a lot.
You earned pizza - when you come to Croatia

Thank you,... For the free food😂.

123abc
Resident Rockstar
Resident Rockstar

In Power BI, you can achieve this by creating a new calculated column in your table that counts how many times a value from one column appears in another column. In your case, you want to count how many countries each product appears in. Here's how you can do it:

  1. Open your Power BI report and go to the "Model" view.

  2. In the "Fields" pane, select the table where you want to create the new calculated column.

  3. Click on the "Modeling" tab in the top menu.

  4. Click on "New Column."

  5. In the formula bar that appears at the top, enter the following DAX formula:

Country Count = COUNTROWS( FILTER( YourTableName, YourTableName[Product] = EARLIER(YourTableName[Product]) ) )

 

Replace YourTableName with the actual name of your table and Product with the name of the column containing the product names.

  1. Press Enter to create the new column.

This DAX formula creates a new column called "Country Count" that counts how many times each product appears in the same table based on the product name. It uses the COUNTROWS function along with the FILTER function to count the rows where the product name matches the current row's product name.

Now, you should see a new column in your table that shows the count of countries for each product. You can use this column in your visuals to see how many countries each product is sold in.

Thanks, your proposal is giving me how many times at all apears in some country (can be multiple times in one country)
Altought ...

"CountryCount=calculate(DistinctCOUNT('Table'[Country]), Filter('Table''Table'[Product] = EARLIER('Table'[Product])))" it is counting some country just once.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.