cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Countifs Functiion (Dynamic by every row)

1. I want to count "every date" by "each country" when "Confirmed column is nonblank or >0". In excel file we can do it by countifs function. What would be the Dax formula for this solution?

 Date Confirmed Recovered Deaths Day of Year Province Country Corona Days (Expected Result) 22-01-20 5 Bangladesh 1 22-01-20 Sweden 0 22-01-20 US 0 22-01-20 UK 0 22-01-20 417 India 1 22-01-20 694 Italy 1 22-01-20 411 Denmark 1 23-01-20 229 Norway 1 23-01-20 146 UAE 1 23-01-20 265 Bangladesh 2 23-01-20 388 Mali 1 23-01-20 358 Italy 2 24-01-20 599 UK 2 24-01-20 371 Bangladesh 3 24-01-20 776 Sweden 1
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

## Re: Countifs Functiion (Dynamic by every row)

Kudos and Thanks a lot for your support. Finally I have got my expected result by the following formula:

Corona Days =
if('Covid-19 Cases'[Confirmed]<=0,0,(
calculate(
DISTINCTCOUNT('Covid-19 Cases'[Date]),
(FILTER('Covid-19 Cases','Covid-19 Cases'[Country/Region] =
EARLIER('Covid-19 Cases'[Country/Region]) && 'Covid-19 Cases'[Date] <=
EARLIER('Covid-19 Cases'[Date]) && 'Covid-19 Cases'[Confirmed]>0)))))

Without using "Distinctcount" function, proper number was not coming as some of the countries had entry on the state column. On that case, counting date was giving unexpected numbers. you can suggest if there is more efficient dax for it.
Regards
9 REPLIES 9
Highlighted
Super User V

## Re: Countifs Functiion (Dynamic by every row)

Hi @the_mishuk ,

You can try

DC =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Date] ),
FILTER (
ALLEXCEPT (
'Table',
'Table'[Country]
),
'Table'[Confirmed] > 0
|| NOT (
ISBLANK ( 'Table'[Country] )
)
)
)

Pls share sample data in text format incase this does not work

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Highlighted
Frequent Visitor

## Re: Countifs Functiion (Dynamic by every row)

Hi Harshnathani: This DAX returning the total days for a country. But I am expecting to get the number of days as I have updated in another table. (after pasting the table the date column is getting distorted 😞 )

Highlighted
Super User V

## Re: Countifs Functiion (Dynamic by every row)

Hi @the_mishuk ,

DC =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Date] ),

FILTER(
ALLEXCEPT (
'Table',
'Table'[Country]
),

MAX('Table'[Confirmed]) > 0
|| NOT(ISBLANK(MAX('Table'[Confirmed])))

))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Highlighted
Frequent Visitor

## Re: Countifs Functiion (Dynamic by every row)

Hi @harshnathani : Appreciate your faster supports. But seems I am not yet getting expected value. Its same value coming in every cell 😞

Highlighted
Super User V

## Re: Countifs Functiion (Dynamic by every row)

Can you share expected output.

I had shared the formula for a measure.

Regards,

Harsh Nathani

Highlighted
Frequent Visitor

## Re: Countifs Functiion (Dynamic by every row)

I am basically expecting the output like the last column.

Highlighted
Super User V

## Re: Countifs Functiion (Dynamic by every row)

Hi @the_mishuk ,

Create a new Calculated Column.

Output =
CALCULATE(COUNT('Table'[Confirmed]),(FILTER('Table', 'Table'[Country] = EARLIER('Table'[Country]) && 'Table'[Date] <= EARLIER('Table'[Date]) && 'Table'[Confirmed] > 0)))

UK should be 1 on 24th,2020.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Highlighted
Super User V

## Re: Countifs Functiion (Dynamic by every row)

Hi @the_mishuk ,

And incase you want 0's in front of Sweden,US,UK.

Output =

var a = CALCULATE(COUNT('Table'[Confirmed]),(FILTER('Table', 'Table'[Country] = EARLIER('Table'[Country]) && 'Table'[Date] <= EARLIER('Table'[Date]) && 'Table'[Confirmed] > 0)))

var b = IF (ISBLANK(a),0,a)
Return
b

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Highlighted
Frequent Visitor

## Re: Countifs Functiion (Dynamic by every row)

Kudos and Thanks a lot for your support. Finally I have got my expected result by the following formula:

Corona Days =
if('Covid-19 Cases'[Confirmed]<=0,0,(
calculate(
DISTINCTCOUNT('Covid-19 Cases'[Date]),
(FILTER('Covid-19 Cases','Covid-19 Cases'[Country/Region] =
EARLIER('Covid-19 Cases'[Country/Region]) && 'Covid-19 Cases'[Date] <=
EARLIER('Covid-19 Cases'[Date]) && 'Covid-19 Cases'[Confirmed]>0)))))

Without using "Distinctcount" function, proper number was not coming as some of the countries had entry on the state column. On that case, counting date was giving unexpected numbers. you can suggest if there is more efficient dax for it.
Regards

Announcements

#### Microsoft Ignite

This will be a conference that you do not want to miss!

#### Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors