topic Count and DAX in DAX Commands and Tips

Count and DAX

Anonymous — Sun, 29 Jan 2023 20:01:48 GMT

Hi Guys, I am facing issue counting the below rows where the condition is like:

Count number of rows for each group name where

Open date <= Date and resolve date is >= Date. So basically the count for 01-01-2020 would be 30 and for 02-01-2020 should also be 30 even though there is no open date or resolve date for that day.

I have created this table with required columns from the calendar and the fact table.

Appreciate your help in advance.

Re: Count and DAX

FreemanZ — Sun, 29 Jan 2023 20:27:35 GMT

hi Anonymous

unable to follow. Could you elaborate how you get 30 in your example date, say 01-01-2020?

Re: Count and DAX

Anonymous — Sun, 29 Jan 2023 20:53:57 GMT

So the count is basically 30 since the open date is equal to Date and resolve dates are greater than Date. So count of all the tickets falling as per above criteria is 30 for that group name.

Re: Count and DAX

tamerj1 — Mon, 30 Jan 2023 08:17:09 GMT

Hi Anonymous
For a calculated column please use

Count = COUNTROWS ( FILTER ( CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[FactTable.Group Name] ) ), 'Table'[FactTable.Open Date] <= 'Table'[Date] && 'Table'[FactTable.Resolve Rate] >= 'Table'[Date] ) )

For a measure you can use

Count = COUNTROWS ( FILTER ( 'Table', 'Table'[FactTable.Open Date] <= 'Table'[Date] && 'Table'[FactTable.Resolve Rate] >= 'Table'[Date] ) )

Re: Count and DAX

Anonymous — Tue, 31 Jan 2023 01:25:01 GMT

Hi Anonymous ,

I have created a simple sample, please refer to it to see if it helps you.

Create measures.

Measure = CALCULATE ( COUNT('Table'[date]), FILTER ( ALL ( 'Table' ), 'Table'[date] = SELECTEDVALUE ( 'Table'[open date] ) && 'Table'[date] <= SELECTEDVALUE ( 'Table'[close date] ) && 'Table'[date] = SELECTEDVALUE ( 'Table'[date] ) ) )

Measure 2 = COUNTAX(FILTER(ALL('Table'),[Measure]<>BLANK()),[Measure])

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.