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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Creating new table with counts of distinct values; help

Hello all,

 

I have a table with 30 000 rows.  One column in this row has a string value.  There are only 40 some distinct values that can be in this column.

I want to create a new table, which lists the distinct values in one column, and then in the second column, a count of how many times that value appears.  I am very new to PowerBI, and have looked at perhaps a dozen tutorials and forum posts with no solution...

 

So far, I have created the new table trying to show the distinct values in the first column:

 

Table = DISTINCT(Sheet1[StringValue])

 

My latest attempt to fill the 2nd column with the corresponding counts failed when I tried this:

 

Column = calculate(countrows(sheet1),allexcept(Sheet1,Sheet1[StringValue]))

 

Also, I must be missing something fundamental about Tables.  My table is blank until I add that 2nd column.  I assumed that Table = DISTINCT(Sheet1[StringValue]) would return at least one column with data...  but it doesn't.  Only after I add a second column will any data be present.

 

Thank you in advance

1 ACCEPTED SOLUTION

ok so something like =SUMMARIZE(FILTER(Table1,Table1[StingValue]<>blank()),Table1[StingValue]) should work

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

7 REPLIES 7
Kobur
New Member

The following worked for me;

example. Say you have a table Q3, with a column Manufacturer which repeats and you want to create a table called Manufacturers with only Distinct Manufactures and the occurrence of each

Go to "new table" under "modeling" tab

Use the expression below

 

Manufacturers = SUMMARIZE(Q3, Q3[Manufacturer], "Totals", COUNT(Q3[Manufacturer]))

 

This will create a table as below

 

 

Manufacturers           Totals

Daimler                        256

BMW                            200

samdthompson
Memorable Member
Memorable Member

Hi welcome to our community. Try making a new table:

 

1. on ribbon click modelling

2. click New Table

3. enter formula =SUMMARIZE(Sheet1[StringValue])

4. Create relationship between the tables based on the string

5. Create second column on the new table =Calculate(counta(stringValue),filter(Sheet1))

 

This should give you the distinct list. Perhaps the better solution would be to make a table or matrix in powerbi. it will give the you ability to show a count pretty easily.

// if this is a solution please mark as such. Kudos always appreciated.
Anonymous
Not applicable

Thank you for the welcome and for the reply.

 

Unfortunately, I can't create a relationship because there is no column in the original speadsheet with all unique values.  I suppose I could add a column to that in order to create one with unique values...  but is this necessary?

 

I tried to create the 2nd column anyway usuing your calculate() formula, but it appears that it needs a 2nd argument for filter().

 

It's all a bit mind numbing since if I throw the column [stringvalue] into a pie chart it shows me the counts for each value without a problem!  The trick seems to be getting it in a tabular form...

You can create a relationship as you are creating a unique table of values. Its the =summarize() that will create your unique ID.

 

The format for the argument containing the filter will be =Calculate(counta(sheet1[stringValue]),filter(Sheet1)=table[StringValue])

// if this is a solution please mark as such. Kudos always appreciated.
Anonymous
Not applicable

Ah right, but I have a null/blank value generated from summarize, so I can't use that as my unique ID.  Is there a way to filter out blank values from the summarize function?

ok so something like =SUMMARIZE(FILTER(Table1,Table1[StingValue]<>blank()),Table1[StingValue]) should work

// if this is a solution please mark as such. Kudos always appreciated.
Anonymous
Not applicable

Perfect.  Thank you.

I also, managed to get the counts in the 2nd column using CALCULATE(Counta(Sheet1[StringValue]),ALLEXCEPT('Table','Table'[StringValue]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.