cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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
Highlighted
Super User IV
Super User IV

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

Should be something like:

 

 

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

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper III
Helper III

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

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
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors