Forum Discussion
Count records
I have a five years data from 2020 to 2024.I want to count number of records in 2020, 2021,2022,2023 and 2024 separately. in power bi dax
Hi MAP ,
you can simply use Count function
Count_2020 = CALCULATE(COUNTROWS(YourTable), YEAR(YourTable[DateColumn]) = 2020)
Count_2021 = CALCULATE(COUNTROWS(YourTable), YEAR(YourTable[DateColumn]) = 2021)and so on... or
just put a slicer of year
RecordCount =
VAR SelectedYear = SELECTEDVALUE(YourTable[YearColumn])
RETURN
CALCULATE(COUNTROWS(YourTable), YEAR(YourTable[DateColumn]) = SelectedYear)Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Record count: = COUNTROWS(sales_fact)Record count 2022: = CALCULATE( [Record count:], 'calendar'[Year] = 2022)Rocord count 2024: = CALCULATE([Record count:], 'calendar'[Year] = 2024 )
2 Replies
- manvishah17
Solution Supplier
Hi MAP ,
you can simply use Count function
Count_2020 = CALCULATE(COUNTROWS(YourTable), YEAR(YourTable[DateColumn]) = 2020)
Count_2021 = CALCULATE(COUNTROWS(YourTable), YEAR(YourTable[DateColumn]) = 2021)and so on... or
just put a slicer of year
RecordCount =
VAR SelectedYear = SELECTEDVALUE(YourTable[YearColumn])
RETURN
CALCULATE(COUNTROWS(YourTable), YEAR(YourTable[DateColumn]) = SelectedYear) - Jihwan_Kim
Super User
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Record count: = COUNTROWS(sales_fact)Record count 2022: = CALCULATE( [Record count:], 'calendar'[Year] = 2022)Rocord count 2024: = CALCULATE([Record count:], 'calendar'[Year] = 2024 )