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.
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.
Should be something like:
CountNumbers =
VAR __NumberToCount = [NumberToCount]
RETURN
COUNTROWS(
FILTER(ALL('Table'),[NumberToCount] = __NumberToCount)
)
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]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |