Forum Discussion

MAP's avatar
MAP
Frequent Visitor
2 years ago
Solved

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's avatar
    manvishah17
    Icon for Solution Supplier rankSolution 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)

  • 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 )