cancel
Showing results for
Did you mean:
Highlighted
Helper III

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

I have the following real table.

 StartValue EndValue Percentage NumberToCount 1980 2010 0,554893 10 1981 2011 0,145216 10 1982 2012 0,113458 10 1983 2013 0,118518 11 1984 2014 0,948498 11 1985 2015 0,543353 12 1986 2016 0,273374 13 1987 2017 0,239495 14 1988 2018 0,345646 14 1989 2019 0,454435 15

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

 StartValue EndValue Percentage Number CountNumbers 1980 2010 0,554893 10 3 1981 2011 0,145216 10 3 1982 2012 0,113458 10 3 1983 2013 0,118518 11 2 1984 2014 0,948498 11 2 1985 2015 0,543353 12 1 1986 2016 0,273374 13 1 1987 2017 0,239495 14 2 1988 2018 0,345646 14 2 1989 2019 0,454435 15 1

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

2 REPLIES 2
Highlighted
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)
)
``````

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

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

Proud to be a Super User!

Highlighted
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]``

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### 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