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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
gluizqueiroz
Resolver I
Resolver I

How to count how much times a number appears in a column ignoring the row context?

I have the following real table.

 

StartValueEndValuePercentageNumberToCount
198020100,55489310
198120110,14521610
198220120,11345810
198320130,11851811
198420140,94849811
198520150,54335312
198620160,27337413
198720170,23949514
198820180,34564614
198920190,45443515


I need to create a calculated column that counts how much times a number appears in the column "NumberToCount", like the following result:

 

StartValueEndValuePercentageNumberCountNumbers
198020100,554893103
198120110,145216103
198220120,113458103
198320130,118518112
198420140,948498112
198520150,543353121
198620160,273374131
198720170,239495142
198820180,345646142
198920190,454435151


It can't be a measure, because I need to create a calculated column.

2 REPLIES 2
Greg_Deckler
Super User
Super User

Should be something like:

 

 

CountNumbers = 
VAR __NumberToCount = [NumberToCount]
RETURN
COUNTROWS(
  FILTER(ALL('Table'),[NumberToCount] = __NumberToCount)
)

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hey @Greg_Deckler .

Thanks for answering me. Your solutions really works, but not in my case.
I didn't mentioned on my question how I create a table because I think it can be more difficult to understand my question, but, let me tell you.

I have been creating my table using variables and ALL and FILTER and ADDCOLUMNS, I have learned this strategy by reading your answers here, on Power BI Forum.

 

So, my table has the following code, for example:

Table = 
VAR __table1 = ALL(BI_Installments[YearSold]; BI_Installments[YearExpiry]; BI_Installments[Sold-Expiry])
VAR __table2 = ADDCOLUMNS(__table1; "%VI"; [% Value Installments])
VAR __table3 = FILTER(__table2; AND([Sold-Expiry] > 0; [%VI] > 0))

VAR __table32 = ADDCOLUMNS(__table3; "AuxCount"; ----- INSERT THE COUNT HERE -----)
VAR __table33 = FILTER(__table32; AuxCount > 1)

VAR __table4 = GROUPBY(__table3; [Sold-Expiry]; "%FinalVI"; AVERAGEX(CURRENTGROUP(); [%VI]))
VAR __table5 = SELECTCOLUMNS(__table4; "Total"; DIVIDE([%FinalVI]; 1; 0))
RETURN
__table5


I think you will understand this type of creating a table using DAX (in the future, I will transform this in a measure).
Following you answer, I can't use:

VAR __NumberToCount = [NumberToCount]

In my case:

VAR __NumberToCount = [Sold-Expiry]

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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