cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Countifs Functiion (Dynamic by every row)

Countifs.JPG

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?

 

DateConfirmedRecoveredDeathsDay of YearProvinceCountryCorona Days (Expected Result)
22-01-205    Bangladesh1
22-01-20     Sweden0
22-01-20     US0
22-01-20     UK0
22-01-20417    India1
22-01-20694    Italy1
22-01-20411    Denmark1
23-01-20229    Norway1
23-01-20146    UAE1
23-01-20265    Bangladesh2
23-01-20388    Mali1
23-01-20358    Italy2
24-01-20599    UK2
24-01-20371    Bangladesh3
24-01-20776    Sweden1
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Countifs Functiion (Dynamic by every row)

Hi @harshnathani :

 

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
Capture 2.JPG

View solution in original post

9 REPLIES 9
Highlighted
Super User V
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
Super User V

Re: Countifs Functiion (Dynamic by every row)

Hi @the_mishuk ,

 

Please create a measure

 

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

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

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

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 😞

Capture 2.JPG

Highlighted
Super User V
Super User V

Re: Countifs Functiion (Dynamic by every row)

@the_mishuk ,

 

 

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)

@harshnathani 

 

I am basically expecting the output like the last column.

 

Capture.JPG

Highlighted
Super User V
Super User V

Re: Countifs Functiion (Dynamic by every row)

Hi @the_mishuk ,

 

 

Missed replying back yesteday, had an electricity outage.

 

Here are your results.

 

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)))
 
 
1.jpg
 
 
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
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)

Hi @harshnathani :

 

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
Capture 2.JPG

View solution in original post

Helpful resources

Announcements
Ignite

Microsoft Ignite

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

Get Ready for Power BI Dev Camp

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.