Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Shota_Xuc
Helper I
Helper I

Count rows with same value in one column

Hello,

 

I have a column "Valuation date" with dates like:

2024-02-06

2024-02-06

2024-02-06

2024-02-07

2024-02-07

2024-02-07

2024-02-09

2024-02-10

2024-02-10

2024-02-12

2024-02-12

2024-02-12

2024-02-12

I want to create a measure showing how many times i have this date in the column like:

2024-02-06     3

2024-02-06     3

2024-02-06     3

2024-02-07     3

2024-02-07     3

2024-02-07     3

2024-02-09     1

2024-02-10     2

2024-02-10     2

2024-02-12     4

2024-02-12     4

2024-02-12     4

2024-02-12     4

 

Please help with DAX. Thanks!

11 REPLIES 11
NickDanger
Regular Visitor

I'm assuming it's intended that you want the dates to display multiple times as well in the visual. This would typically mean either
a) you're not using a date table or
b) there are going to be more columns added to the display table and a date table is going to be used.

Either way, the following calculation should do the job for you. 'Table' in this case is going to be the table that your fact is (I.E. not the date table)

CountOfDates = 
var _SelectedValueDate = SELECTEDVALUE('Table'[Date])
return
CALCULATE(
     COUNTROWS('Table'),
     ALL('Table'),
     'Table'[Date] = _SelectedValueDate
)



Hi @NickDanger, it does not work. E.g. I should get 3 for 02-06, 1 for 02-09, 2 for 02-13 instead.

I think the expression takes numbers from ground table my visual is based on, but rows in my visual are filtered out. I need to count rows with the same valuation date on my visual below.

Shota_Xuc_0-1708586819886.png

 

@Shota_Xuc 
Thanks for the elaboration. The original post did have much info. Here is a code snippest of test data from AdventureWorks that I mocked up that would work with your issue.

Test Measure = 
var _SelectedValueDate = SELECTEDVALUE('FactInternetSales'[OrderDate])
RETURN
CALCULATE(
    COUNTROWS('FactInternetSales'),
    ALLEXCEPT('FactInternetSales', 'FactInternetSales'[PromotionKey]), //Add any columns here that you're filtering on
    'FactInternetSales'[OrderDate] = _SelectedValueDate)

Include any of the columns you're filtering your table on in the ALLEXCEPT function. For instance, in my data I filtered on PromotionKey. 
This first screenshot is without including PromotionKey in the ALLEXCEPT, the second screenshot is when it's written to account for the filter.

NickDanger_1-1708610030547.png

 

NickDanger_2-1708610068151.png

 

@NickDanger 

it does not work, I have adjusted your code to my visual, here is my code:

Test Measure =
var _SelectedValueDate = SELECTEDVALUE('SFX_TRS_RESET_MATURITY_DATES'[Valuation date])
RETURN
CALCULATE(
    COUNTROWS('SFX_TRS_RESET_MATURITY_DATES'),
    ALL('SFX_TRS_RESET_MATURITY_DATES'), //Add any columns here that you're filtering on
    'SFX_TRS_RESET_MATURITY_DATES'[Valuation date] = _SelectedValueDate)
and I get wrong numbers:
Shota_Xuc_0-1708614843939.png

 

 

Sorry if there was confusion, but your code does not match mine. You need to use 

ALLEXCEPT('SFX_TRS_RESET_MATURITY_DATES', 'SFX_TRS_RESET_MATURITY_DATES'[FILTERED COLUMN HERE]])


Not ALL('SFX_TRS_RESET_MATURITY_DATES')

If you're filtering on more than one column, separate them with a comma after each column listed. 

@NickDanger 

I am not filtering on more than one column, 

I tried the below code, but I get wrong numbers:

 

Test Measure =
var _SelectedValueDate = SELECTEDVALUE('SFX_TRS_RESET_MATURITY_DATES'[Valuation date])
RETURN
CALCULATE(
    COUNTROWS('SFX_TRS_RESET_MATURITY_DATES'),
    ALLEXCEPT('SFX_TRS_RESET_MATURITY_DATES', SFX_TRS_RESET_MATURITY_DATES[Valuation date]), //Add any columns here that you're filtering on
    'SFX_TRS_RESET_MATURITY_DATES'[Valuation date] = _SelectedValueDate)

 

Shota_Xuc
Helper I
Helper I

@bhanu_gautam @gmsamborn 

 

yes, exactly, that does not work as a measure because it "refers to an earlier row context which doesn't exist." But I do not need to have it as a calculcated column, if I have it as a column, I get numbers from the ground table (which has more rows than my visual) and not from my visual.

Hi @Shota_Xuc 

 

Try changing EARLIER to MAX.



Proud to be a Super User!

daxformatter.com makes life EASIER!

Hi @gmsamborn MAX does not work either, as you see I get 2 instead of 3 for 2024-02-06, 4 instead of 1 for 2024-02-09, 6 instead of 2 for 2024-02-13 and etc.

Shota_Xuc_1-1708587269327.png

 

bhanu_gautam
Super User
Super User

@Shota_Xuc , You can try below mentioned measure

 

Count of Valuation Dates =
COUNTROWS(
FILTER(
'YourTableName',
'YourTableName'[Valuation date] = EARLIER('YourTableName'[Valuation date])
)
)

 

Please accept as solution and give kudos if it helps




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Hi @bhanu_gautam 

 

That DAX won't work for a measure because it "refers to an earlier row context which doesn't exist."

 

It would work as a calculated column.



Proud to be a Super User!

daxformatter.com makes life EASIER!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.